January 28, 2010 at 1:24 am
I have one table test described in below,which has two columns date1 and id .I would like to retrive 2nd highset id
per day.It is like 2nd exmaple.
date1 id
------------------------------------------------------ -----------
2010-01-24 10:31:36.763 1
2010-01-24 10:31:41.480 2
2010-01-24 10:31:45.560 3
2010-01-24 10:31:50.013 4
2010-01-25 10:31:57.700 1
2010-01-25 10:32:01.640 2
2010-01-25 10:32:05.780 3
2010-01-26 10:32:11.250 1
2010-01-26 10:32:16.013 5
2010-01-26 10:32:19.983 7
2010-01-27 10:32:26.343 9
2010-01-27 10:32:29.873 10
2010-01-27 10:32:33.233 11
Date 2nd_Highest_ID
2010-01-243
2010-01-252
2010-01-265
2010-01-2710
January 28, 2010 at 1:46 am
I wont tell you the answer, but here are some clues.
Investigate the row_number() function.
Wrap the query in a CTE
Filter on 'row_number = 2'
January 28, 2010 at 11:06 am
I know the logic to find out,the logic is:
1.find the highest id per day.
2. Then find the max of (the original table -highest id table)
this way we get the 2nd highest table..
but how i have to write the query...?
January 28, 2010 at 11:25 am
Can you show us what you have attempted so far ?
Did you read up on row_number() ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply