only want one result

Posted on 2012-08-16
Last Modified: 2012-08-16
select column from table order by column desc

I only want the fifth result
Question by:rgb192
    LVL 5

    Accepted Solution

    SELECT *
    FROM   ( SELECT Row_number( ) OVER ( ORDER BY <FIELD>) AS Row,
             FROM   <TABLE> ) AS temp
    WHERE  ROW = 5 

    Open in new window

    LVL 25

    Expert Comment

    You can give the row_number and select the 5th row.

    ; With CTE as
                      select column1, column2,...., columnN,
                      RN = ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY requiredColumn DESC)
    select * from CTE
    where RN = 5
    LVL 65

    Expert Comment

    by:Jim Horn
    If you mean fifth result as the fifth row returned, with no special ordering, then...

    SELECT column
    FROM (
          select column, row_number() OVER (ORDER BY (SELECT 0)) as row_number
          FROM table) a
    WHERE a.row_number = 5

    If there is any special ordering, then replace the SELECT 0 part with however you wish to order.
    LVL 25

    Expert Comment


          top 5 *
    into #temp_tbl
    from YourTable
    order by column desc

    select top 1 * from #temp_tbl order by column
    LVL 92

    Expert Comment

    by:Patrick Matthews
    select top 1 z.column
    from (select top 5 column from table order by column desc) z
    order by z.column

    Author Closing Comment

    thank you for quick answer

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now