only want one result

select column from table order by column desc

I only want the fifth result
LVL 1
rgb192Asked:
Who is Participating?
 
Jason SchlueterConnect With a Mentor IT ManagerCommented:
SELECT *
FROM   ( SELECT Row_number( ) OVER ( ORDER BY <FIELD>) AS Row,
                *
         FROM   <TABLE> ) AS temp
WHERE  ROW = 5 

Open in new window

0
 
TempDBACommented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
TempDBACommented:
Or,

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


select top 1 * from #temp_tbl order by column
0
 
Patrick MatthewsCommented:
select top 1 z.column
from (select top 5 column from table order by column desc) z
order by z.column
0
 
rgb192Author Commented:
thank you for quick answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.