Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: 10 top results query - HOW?

    i have a table in this format

    status | count
    A | 10
    B 12
    C 15
    .
    .
    .

    the question is:
    i need a query that does the following:
    select from this table the 10 highest counts and there status
    and they also should be in desc order

    can any one help me?
    10X

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select * 
    from (select status, count_field from table order by count_field desc)
    where rownum < 11
    Although that only gets you the first 10. If there are any records which share the same value for count, you probably want those to display, which means you might have more than 10 records appearing.

    This SQL gives you the n'th highest value (the 5th highest in this example)
    Code:
    SELECT MIN(field1) FROM
    (SELECT distinct field1
    FROM table1
    ORDER BY field1 DESC)
    WHERE rownum <= n
    So you could incorporate that to get all records with the 10 highest values
    Code:
    SELECT * 
    FROM TABLE 
    WHERE count_field <= (SELECT MIN(count_field) 
                                   FROM (SELECT distinct count_field
                                             FROM table
                                             ORDER BY count_field DESC)
                                   WHERE rownum <= 5)
    -cf
    Last edited by chuck_forbes; 09-28-05 at 11:56.

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279

Posting Permissions

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