We help IT Professionals succeed at work.

only want one result

Medium Priority
596 Views
Last Modified: 2012-08-16
select column from table order by column desc

I only want the fifth result
Comment
Watch Question

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

Open in new window

Commented:
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
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.

Commented:
Or,

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


select top 1 * from #temp_tbl order by column
CERTIFIED EXPERT
Top Expert 2010

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

Author

Commented:
thank you for quick answer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.