Post reply

Get the 5th highest column value from a table..

  • August 29, 2005 at 12:38 am

    #165825

    hi..

    I want to fetch the fifth highest value of some column (<column name> from a table <tablename>..

    I have worked it like this..

    select * from <tablename>

    where <column name> in (select min( <column name> ) from staff where

    <column name> in(select top 5(max(column name)) from staff group by

    <column name> order by <column name> desc))

     

    This works..

    Is there any other way of doing this? Please suggest..

     

    Thanks..

  • August 29, 2005 at 6:13 am

    #585782

    Hi

    I'm doing it with a temp table:

    set rowcount 5

    select field1 into #temp2

    from #temp order by field1 desc

    select min( field1 ) from #temp2

    Hope this helps too.

    Roger

  • August 29, 2005 at 6:23 am

    #585786

    Perfect!!

    It works..

    Thank you..

     

  • August 29, 2005 at 6:36 am

    #585789

    Hi again

    I just tried another which I think is more elegant:

    select min(fid) from (select top 5 * from #temp order by fid desc) as test

    So you don't need to go over temp table and you don't need to define rowcount

    Roger

  • August 29, 2005 at 6:44 am

    #585794

    yup..this one too works..

    Similar to the query i posted to start with..except for the "max" that i have used, which is not necessary..

    thanks again..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply