By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,910 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,910 IT Pros & Developers. It's quick & easy.

Getting the second latest date in a group

P: 11
Hi All,

Currently, I have a SQL query where I need to grab the SECOND latest date by group.

COMPONENTID COMPLETIONDATE
CBT_HAZCOM 02/26/2007 15:17:00
CBT_HAZCOM 07/31/2007 21:23:00
QS 02/12/2007 13:51:00
SHEN158 02/12/2007 13:29:00

What I want to do from this table is grab the second latest date under the completiondate column ID grouped by CBT_HAZCOM. If there is only 1 entry for COMPONENTID (like for QS and SHEN158), it should keep the current COMPLETIONDATE

The resulting output should be:

COMPONENTID COMPLETIONDATE
CBT_HAZCOM 02/26/2007 15:17:00
QS 02/12/2007 13:51:00
SHEN158 02/12/2007 13:29:00

Please note that CBT_HAZCOM with COMPLETIONDATE removed 07/31/2007 because it was the latest date in the CBT_HAZCOM grouping. The second latestdate is 02/26/2007.

Please help as this is critical in what I am trying to accomplish!

Sincerely,

Piyush A.
Aug 4 '07 #1
Share this Question
Share on Google+
6 Replies


P: 11
Sorry for the miscommunication. The wording may have been a little off:

"What I want to do from this table is grab the second latest date under the completiondate column ID grouped by COMPONENTID. If there is only 1 entry for COMPONENTID (like for QS and SHEN158), it should keep the current COMPLETIONDATE"
Aug 4 '07 #2

pbmods
Expert 5K+
P: 5,821
Heya, Piyush.

If getting the latest date looks something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `COMPONENTID`,
  3.         `COMPLETIONDATE`
  4.     FROM
  5.         `Table`
  6.     ORDER BY
  7.         `COMPLETIONDATE` DESC
  8.     LIMIT 1;
  9.  
Then getting the second-to last date looks like this (note the LIMIT clause):
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         `COMPONENTID`,
  3.         `COMPLETIONDATE`
  4.     FROM
  5.         `Table`
  6.     ORDER BY
  7.         `COMPLETIONDATE` DESC
  8.     LIMIT 1,1;
  9.  
The first number tells MySQL to offset the results by one. The second number tells MySQL to return only one result.
Aug 5 '07 #3

P: 11
Very much appreciated, however is the LIMIT command only for My SQL? Currently, I need my code universal to Oracle Databases and the Limit 1,1 will not work.

Is there an alternative without having to use LIMIT 1,1? I have spent numerous hours trying to figure this out; any help is appreciated.

This is the SQL code I used to determine the max value:

select pa_cpnt_evthst.cpnt_id as componentid,MAX (pa_cpnt_evthst.compl_dte) as completiondate
FROM pa_cpnt_evthst, pa_cpnt
WHERE pa_cpnt_evthst.cpnt_id = pa_cpnt.cpnt_id
AND pa_cpnt_evthst.stud_id = '10203996'
AND pa_cpnt.notactive = 'N'
AND pa_cpnt.retrng_int IS NOT NULL
AND pa_cpnt.retrng_int <> 0
AND ((pa_cpnt_evthst.cpnt_typ_id = 'CBT'AND pa_cpnt_evthst.cmpl_stat_id IN ('09', '10'))OR (pa_cpnt_evthst.cpnt_typ_id <> 'CBT'))
group by pa_cpnt_evthst.cpnt_id
Aug 6 '07 #4

pbmods
Expert 5K+
P: 5,821
Heya, piyushdabomb.

I'm going to go ahead and move this thread to the Oracle forum, where our resident Experts will be better able to help you out.
Aug 6 '07 #5

100+
P: 153
Try this out. It uses an analytic function to number the rows by date desc, grouped by the component. then i simply limit the count to 2 rows per group and take the minimum.

Expand|Select|Wrap|Line Numbers
  1. SELECT   MIN( componentid) componentid,
  2.          MIN( completiondate) completiondate
  3. FROM     (SELECT componentid,
  4.                  completiondate,
  5.                  ROW_NUMBER() OVER(PARTITION BY componentid ORDER BY completiondate DESC) my_row_num
  6.           FROM   temp)
  7. WHERE    my_row_num <= 2
  8. GROUP BY componentid;
  9.  
Aug 7 '07 #6

P: 11
Dave44:

After much research you've brought a ton of insight as to using "rows" instead of playing with subselects and outerjoins...Online Analytical Processes are definitely the way to go (from your suggestions).

My questions now bear:-

> Do you have any good links that could help me optimize my code?
> How do I know for example that an outerjoin is going to be quicker than using windows functions?
> What difficulty level would you classify Analytical processes if we were to categorize them - Beginner, Intermediate, Above Average, Guru

I seem to use a TON of subselects but also takes my query 3 minutes to run. Potentially, I could increase the speed of my queries if I knew the correct alternatives.
Aug 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.