2nd Largest value

Hi,
How do i retrieve 2nd Largest Amount in a table using Query:
Table: sales
Sno. Name Amount
111 abc 10000
222 xyz 23000
333 mbn 100
444 iriri 50000
-DNK
CREATE TABLE #SampleTable
(
SomeValue INT
)
INSERT INTO Select 10
INSERT INTO #SampleTable Select 29
INSERT INTO #SampleTable Select 8
INSERT INTO #SampleTable Select 8
INSERT INTO #SampleTable Select 9
INSERT INTO #SampleTable Select 50
Select TOP 1 SomeValue from
(
Select TOP 2 SomeValue from #SampleTable Order by SomeValue
DESC
) sv
Order by SomeValue ASC
--OR
Select TOP 1 SomeValue from #SampleTable
WHERE SomeValue not in
(
Select TOP 1 SomeValue from #SampleTable Order by SomeValue
DESC
)
Order by SomeValue DESC
HTH, Jens Suessmeyer.|||Try:
select
*
from
dbo.MyTable a
where
2 = (select count (*)
from dbo.MyTable b
where b.Amount > a.Amount
or (b.Amount = a.Amount
and b.Sno > a.Sno)
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"DNKMCA" <dnk@.msn.com> wrote in message
news:e7G4WppvFHA.3236@.TK2MSFTNGP14.phx.gbl...
Hi,
How do i retrieve 2nd Largest Amount in a table using Query:
Table: sales
Sno. Name Amount
111 abc 10000
222 xyz 23000
333 mbn 100
444 iriri 50000
-DNK|||Try this:
select top 1 Amount
from (
select top 2 Amount
from dbo.sales
order by Amount desc
) topTwo
order by topTwo.Amount
ML|||Try one of the following. If there are duplicate amounts then the
second query may return more than one row or there may be more than one
row with a higher amount than that returned. This may or may not be
what you want - it all depends on how you define "2nd largest".
SELECT MAX(amount)
FROM sales
WHERE amount <
(SELECT MAX(amount)
FROM sales) ;
SELECT sno, name, amount
FROM sales
WHERE amount =
(SELECT MAX(amount)
FROM sales
WHERE amount <
(SELECT MAX(amount)
FROM sales)) ;
David Portas
SQL Server MVP
--|||DNKMCA
try this
select min(a.amount) from
(select top 2 amount from pay503)a
Regards
R.D
"DNKMCA" wrote:

> Hi,
> How do i retrieve 2nd Largest Amount in a table using Query:
> Table: sales
> Sno. Name Amount
> 111 abc 10000
> 222 xyz 23000
> 333 mbn 100
> 444 iriri 50000
> -DNK
>
>|||select min(a.amount) from
(select top 2 amount from SALES)a
"R.D" wrote:
> DNKMCA
> try this
> select min(a.amount) from
> (select top 2 amount from pay503)a
> Regards
> R.D
>
> "DNKMCA" wrote:
>|||OOPS
USE ORDERBY IN DERIVED TABLE
select min(a.amount) from
(select top 2 amount from sales order by amount desc) a
This query will have least overhead of all.
"R.D" wrote:
> DNKMCA
> try this
> select min(a.amount) from
> (select top 2 amount from pay503)a
> Regards
> R.D
>
> "DNKMCA" wrote:
>|||Notice that this will give a different result to my version if there is
a tie for the number 1 spot.
Also notice that your TOP query will give the wrong answer if there is
only one row in the Sales table. Maybe that's not a realistic scenario
in this case but it is an example of how tricky TOP is. TOP has a
number of catches. Another one is that it sorts NULLs first, so TOP 1
in ascending order may give you a different result to MIN.
David Portas
SQL Server MVP
--|||David
>if there is
only one row in the Sales table. Maybe that's not a realistic scenario
My SQL SERVER RETURNS one row only though i use top 2
example
CREATE TABLE #MYTABLE(COL1 INT,COL2 INT)
INSERT INTO #MYTABLE VALUES(1,5)
SELECT TOP 2 COL2 FROM #MYTABLE ORDER BY COL2
result:
5
--

>Notice that this will give a different result to my version if there is
a tie for the number 1 spot.
we have with TIE option too.

> Another one is that it sorts NULLs first, so TOP 1
in ascending order may give you a different result to MIN.
I dont agree test this:
CREATE TABLE #MYTABLE1(COL1 INT,COL2 INT)
INSERT INTO #MYTABLE1 VALUES(1,5)
INSERT INTO #MYTABLE1 VALUES(NULL,5)
INSERT INTO #MYTABLE1 VALUES(NULL,3)
INSERT INTO #MYTABLE1 VALUES(NULL,NULL)
INSERT INTO #MYTABLE1 VALUES(9,NULL)
SELECT MIN(A.COL2) FROM (SELECT TOP 2 COL2 FROM #MYTABLE1 ORDER BY COL2
desc) A
results:
3 (not null)
everything has a catches you if you dont catch it.
Regards
R.D
"David Portas" wrote:

> Notice that this will give a different result to my version if there is
> a tie for the number 1 spot.
> Also notice that your TOP query will give the wrong answer if there is
> only one row in the Sales table. Maybe that's not a realistic scenario
> in this case but it is an example of how tricky TOP is. TOP has a
> number of catches. Another one is that it sorts NULLs first, so TOP 1
> in ascending order may give you a different result to MIN.
> --
> David Portas
> SQL Server MVP
> --
>