Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find second maximum record Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2012 1:52 AM
Points: 2, Visits: 16
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
Post #1384422
Posted Wednesday, November 14, 2012 12:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
Second maximum as ordered by what?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1384428
Posted Wednesday, November 14, 2012 1:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2012 1:52 AM
Points: 2, Visits: 16
order by a required.

i need the result should be as i mentioned
Post #1384432
Posted Wednesday, November 14, 2012 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, Visits: 547
Your question is very unclear.

Maybe you mean something like.
SELECT	MAX(Val)
FROM Table
WHERE Val < (SELECT MAX(Val) FROM Table)





For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1384433
Posted Wednesday, November 14, 2012 1:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1384439
Posted Wednesday, November 14, 2012 1:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1384449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse