November 14, 2012 at 12:28 am
Pls give me a query for to select second maximum of the result set.
a,b is column names.
insert (a,b)values('2','2012-12-21')
insert (a,b)values('2','2012-12-23')
insert (a,b)values('3','2012-11-23')
insert (a,b)values('3','2012-11-21')
insert (a,b)values('2','2012-12-21')
i need to get the result as
'2','2012-12-21'
'3','2012-11-21'
like that i need to get the result. pls give any suggestion
November 14, 2012 at 12:57 am
Second maximum as ordered by what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2012 at 1:02 am
order by a required.
i need the result should be as i mentioned
November 14, 2012 at 1:07 am
November 14, 2012 at 1:21 am
yuvach_74 (11/14/2012)
order by a required.i need the result should be as i mentioned
With the sample data you gave, the second max row when ordered by a will not give the results you mentioned.
The second max row ordered by a would return either '2','2012-12-21' or '2','2012-12-23'
Select distinct a, min(b) from table group by a
will give you what you asked, but I suspect it's not what you want.
Perhaps a more detailed explanation and better sample data would help...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2012 at 1:30 am
Heres one solution, using a Row_Number() to select the second record, but it depends on the order by
Declare @t as Table (a int, b date)
insert into @t (a,b)values('2','2012-12-21')
insert into @t (a,b)values('2','2012-12-23')
insert into @t (a,b)values('3','2012-11-23')
insert into @t (a,b)values('3','2012-11-21')
insert into @t (a,b)values('2','2012-12-21')
;With Cte as (
Select *,Row_Number() Over(Partition by a order by b desc) rn from @t
)
Select a,b from Cte where rn=2
;With Cte as (
Select *,Row_Number() Over(Partition by a order by b asc) rn from @t
)
Select a,b from Cte where rn=2
The first one does a Descending sort the second does an ascending sort so you get different values.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply