[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

only want one result

Posted on 2012-08-16
6
Medium Priority
?
576 Views
Last Modified: 2012-08-16
select column from table order by column desc

I only want the fifth result
0
Comment
Question by:rgb192
6 Comments
 
LVL 5

Accepted Solution

by:
Jason Schlueter earned 2000 total points
ID: 38301265
SELECT *
FROM   ( SELECT Row_number( ) OVER ( ORDER BY <FIELD>) AS Row,
                *
         FROM   <TABLE> ) AS temp
WHERE  ROW = 5 

Open in new window

0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38301266
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38301267
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 25

Expert Comment

by:TempDBA
ID: 38301283
Or,

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


select top 1 * from #temp_tbl order by column
0
 
LVL 93

Expert Comment

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

Author Closing Comment

by:rgb192
ID: 38302103
thank you for quick answer
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

825 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