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)