3 largest value

I can use MAX to find the largest value but Is there a way or function to find 3 largest value from a column?Originally posted by QaAP
I can use MAX to find the largest value but Is there a way or function to find 3 largest value from a column?
There are various ways. One is:
SELECT col FROM tab
WHERE 3 >
(SELECT COUNT(DISTINCT col) FROM tab tab2
WHERE tab2.col > tab.col);|||Originally posted by andrewst
There are various ways. One is:
SELECT col FROM tab
WHERE 3 >
(SELECT COUNT(DISTINCT col) FROM tab tab2
WHERE tab2.col > tab.col);

I need to get 3 largest values from 1 table and 1 column. eg.

Table1:
Name Age
abc 3
DAJ 8
YYY 19
TTT 25

3 largest column

Name AGE
----
DAJ 8
YYY 19
TTT 25|||OK, so just change the columns in my previous answer:

SELECT name, age
FROM table1
WHERE 3 >
( SELECT COUNT(DISTINCT age)
FROM table1 t1
WHERE t1.age > table1.age
);|||dear friend,

The printing 3rd largest number not working in MySQL5.
************************************************** ******************************
select * from fun;
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
| 134 |
| 777 |
| 888 |
| 666 |
| 555 |
| 99999 |
| 256 |
| 777 |
+---+
11 rows in set (0.00 sec)

************************************************** **********************************************

select f1.money from fun as f1 where 3>(select count(distinct f1.money) from fun as f1, fun as f2 where f1.money>f2.money order by f1.money desc);

Result is : Empty set (0.01 sec)

Please reply what is the solutin|||I want to print the 3rd largest number of a column say money from table fun as given.

printing 3rd largest number not working
Pls help

The printing 3rd largest number not working in MySQL5.
************************************************** ******************************
select * from fun;
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
| 134 |
| 777 |
| 888 |
| 666 |
| 555 |
| 99999 |
| 256 |
| 777 |
+---+
11 rows in set (0.00 sec)

************************************************** **********************************************

select f1.money from fun as f1 where 3>(select count(distinct f1.money) from fun as f1, fun as f2 where f1.money>f2.money order by f1.money desc);

Result is : Empty set (0.01 sec)

Please reply what is the solution|||This smells like homework to me, but I'll give you a small hand by relocating your question to the MySQL forum. There is a MySQL-specific extension to SQL specifically to help with tasks like this.

-PatP|||Same as I posted before really:

select f1.money
from fun as f1
where 3 >
( select count(distinct f2.money)
from fun as f2
where f1.money>f2.money
);|||please don't post the same question into more than one forum

threads merged|||hi,

It is giving the result as:
mysql> select f1.money
-> from fun as f1
-> where 3 >
-> ( select count(distinct f2.money)
-> from fun as f2
-> where f1.money>f2.money
-> );
+---+
| money |
+---+
| 123 |
| 111 |
| 12 |
+---+
3 rows in set (0.00 sec)

(i.e.) It is taking first three rows of the column money. But I want to print 3 greatest number. i.e.
99999
888
777|||somehow, the inequality was incorrectly reversed

this works (i tested it on your sample data) --select distinct money
from fun as f1
where 3 >
( select count(distinct money)
from fun
where f1.money < money
)
order by money desc|||Other solution, without subquery but with (self) join:
SELECT A.age, A.whatever
FROM table1 AS A
INNER JOIN table1 AS B
ON A.age <= B.age
GROUP BY A.age, A.whatever -- make sure this is at least a PK of table1
HAVING count(*) <= 3
If you just want to see the third largest, replace "<= 3" by "= 3".

Depending on the size of your table (and presence of indices etc.) the subquery solution or the join solution might be the most performant one.|||Yet another solution, but unfortunately a platform-specific one (just like the mysql one), now for DB2:
SELECT *
FROM table1
ORDER BY age DESC
FETCH FIRST 3 ROWS ONLYB.T.W., this is the most performant solution of the three when using DB2.|||excuse me? which mysql-specific solution would you be referring to? :)

pat did not move the thread, we are still in the generic SQL forum, and all the solutions offered up until your last have been good, standard sql

granted, there is a cleaner mysql solution, but fondofopensource re-opened a three-year-old thread in the SQL forum as well as starting a duplicate post in the mysql forum, so when i merged the threads, i left the merged thread here

let's wait to see if he/she is interested in the mysql solution too...|||Sorry, then.
You are right.
Actually, I didn't pay much attention to the mysql stuff being said ;)|||no harm, no foul ;)

the "standard sql" forum is of special interest to me, and i will routinely move threads out of it into more appropriate forums when necessary

this was a difficult case, because the question was actually posted into the mysql forum as well as here

so my decision to merge the threads and leave them here was based not on the poster's database but on the sql solution offered earlier in this thread (which the poster was trying to implement)

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
> --
>