Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: 3 largest value

  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: 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?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: 3 largest value

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

  3. #3
    Join Date
    Jan 2003
    Posts
    4

    Re: 3 largest value

    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

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: 3 largest value

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

  5. #5
    Join Date
    Oct 2006
    Posts
    3

    Red face printing 3rd largest number not working

    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

  6. #6
    Join Date
    Oct 2006
    Posts
    3

    Red face to print 3rd largest number from a column in a table (MySql 5.0)

    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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
    );

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please don't post the same question into more than one forum

    threads merged
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2006
    Posts
    3
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Sorry, then.
    You are right.
    Actually, I didn't pay much attention to the mysql stuff being said
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •