Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Location
    India
    Posts
    12

    Exclamation Unanswered: How to find the second largest value in a field !

    Hi,

    i am taking the values from four tables ,

    I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
    salesman who sold for the highest amount.

    Now I want to find the second highest Amount in the field.

    for the highest and lowest we can use the Max and Min funtion.


    for the second highest value, How can I write the query.

    I already check the previous forums. But i couldn't get the idea.

    Kindly reply me

    Thank you very much,
    Chock.
    Chock

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

    Re: How to find the second largest value in a field !

    Originally posted by chock
    Hi,

    i am taking the values from four tables ,

    I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
    salesman who sold for the highest amount.

    Now I want to find the second highest Amount in the field.

    for the highest and lowest we can use the Max and Min funtion.


    for the second highest value, How can I write the query.

    I already check the previous forums. But i couldn't get the idea.

    Kindly reply me

    Thank you very much,
    Chock.
    Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

    SELECT MAX(amount)
    FROM mytab
    WHERE amount != (SELECT MAX(amount) FROM mytab);

    Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

    SELECT amount FROM mytab m1
    WHERE 4 =
    (SELECT COUNT(DISTINCT amount) FROM mytab m2
    WHERE m2.amount > m1.amount
    );

    i.e. get the amount for which there are exactly 4 higher amounts in the table.

  3. #3
    Join Date
    Apr 2003
    Location
    India
    Posts
    12

    Exclamation Hi haing doubt in the Second query !

    Hi,

    You send me two queries, the first query I understand it. But in the second query

    SELECT amount FROM mytab m1
    WHERE 4 =
    (SELECT COUNT(DISTINCT amount) FROM mytab m2
    WHERE m2.amount > m1.amount
    );
    what's m1 and what's m2. In the previous query you didn't use the m1.

    Actually Amount is the Field name we are going to compare and select.
    and mytab is the Table Name.
    I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.


    Thank you very much,
    Chock.
    Chock

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

    Re: Hi haing doubt in the Second query !

    Originally posted by chock
    Hi,

    You send me two queries, the first query I understand it. But in the second query

    SELECT amount FROM mytab m1
    WHERE 4 =
    (SELECT COUNT(DISTINCT amount) FROM mytab m2
    WHERE m2.amount > m1.amount
    );
    what's m1 and what's m2. In the previous query you didn't use the m1.

    Actually Amount is the Field name we are going to compare and select.
    and mytab is the Table Name.
    I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.


    Thank you very much,
    Chock.
    m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

    SELECT amount FROM mytab
    WHERE 4 =
    (SELECT COUNT(DISTINCT amount) FROM mytab
    WHERE mytab.amount > mytab.amount
    );

    ... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tony, you may have confused the issue by jumping from the second highest to the fifth

    here's another way to get the row with the second highest value:
    Code:
    select Salesman, SaleAmount
      from SalesTable
     where SaleAmount = 
           ( select max(SaleAmount)
               from SalesTable
              where SaleAmount <
                   ( select max(SaleAmount)
                       from SalesTable
                   )
           )
    in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

    wouldn't want to nest that too deeply, eh

    i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

    rudy
    http://r937.com

Posting Permissions

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