3 largest value

QaAP · #1 (**permalink**) 03-04-03, 10:02

I can use MAX to find the largest value but Is there a way or function to find 3 largest value from a column?

andrewst · #2 (**permalink**) 03-04-03, 10:18

Quote:

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

QaAP · #3 (**permalink**) 03-04-03, 11:19

Quote:

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

andrewst · #4 (**permalink**) 03-04-03, 11:23

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

fondofopensource · #5 (**permalink**) 10-05-06, 07:37

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

fondofopensource · #6 (**permalink**) 10-05-06, 07:42

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

Pat Phelan · #7 (**permalink**) 10-05-06, 08:02

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

andrewst · #8 (**permalink**) 10-05-06, 08:09

Same as I posted before really:

Code:

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

r937 · #9 (**permalink**) 10-05-06, 08:31

please don't post the same question into more than one forum

threads merged

fondofopensource · #10 (**permalink**) 10-05-06, 09:12

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

r937 · #11 (**permalink**) 10-05-06, 09:32

somehow, the inequality was incorrectly reversed

this works (i tested it on your sample data) --

Code:

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

Peter.Vanroose · #12 (**permalink**) 10-05-06, 10:36

Other solution, without subquery but with (self) join:

Code:

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.

Peter.Vanroose · #13 (**permalink**) 10-05-06, 10:38

Yet another solution, but unfortunately a platform-specific one (just like the mysql one), now for DB2:

Code:

SELECT *
FROM   table1
ORDER BY age DESC
FETCH FIRST 3 ROWS ONLY

B.T.W., this is the most performant solution of the three when using DB2.

r937 · #14 (**permalink**) 10-05-06, 10:47

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

Peter.Vanroose · #15 (**permalink**) 10-05-06, 11:01

Sorry, then.
You are right.
Actually, I didn't pay much attention to the mysql stuff being said

Internet Services

Web Development

Digital Marketing

Consumer Tech