How do I select a specific entry from a select top nn statement

Question

Post reply

How do I select a specific entry from a select top nn statement

PeterR-1037872

SSC Veteran

Points: 282
More actions
March 9, 2009 at 9:34 pm

#206765

I am looking at items returned from a select top 5 list.
How can I look at specific items in that list, for example the second or third item.

Viewing 15 posts - 1 through 15 (of 21 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Here's how to get the third item:

Select TOP 1 * From (

Select TOP 3 * From (

Select TOP 5 * From syscolumns

Order By id, colid)

Order By id,colid DESC

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

An interesting example I had ran into ..

[font="Courier New"]SELECT * FROM (

SELECT

ROW_NUMBER() OVER (ORDER BY ResourceName ASC) AS rownumber,

*

FROM ApplicationResources

) AS foo

WHERE rownumber = 5[/font]

Ref: http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

shankaran_sraj SSC-Addicted Points: 444 More actions · Answer 3

To get Third highest value..

Try this..

SELECT MAX(col_name) FROM Table1 A

WHERE 3 = (SELECT COUNT(*)+1 FROM Table1 B WHERE A.Col_name< B.Col_name)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Mohit: This is a SQL 2000 forum, so no ROW_NUMBER().

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 5

*smack* ... I knew I should have drank more coffee ...

>_> Sorry.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

shankaran_sraj (3/9/2009)
To get Third highest value..
Try this..
SELECT MAX(col_name) FROM Table1 A
WHERE 3 = (SELECT COUNT(*)+1 FROM Table1 B WHERE A.Col_name< B.Col_name)

Will work only of numeric/integer columns. If the data needs to be fetched from char/varchar columns this may not give the desired results.

"Keep Trying"

PeterR-1037872 SSC Veteran Points: 282 More actions · Answer 7

Thanks for your suggestions guys.

I was most impressed with RBarry's solution, but unfortunately nested selects did not work on our SQL2000 site. I got an 'incorrect syntax' error.

I've decided to populate a temp table with each of the values in the original top 5 and join that table into the original query.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

PeterR (3/10/2009)
Thanks for your suggestions guys.
I was most impressed with RBarry's solution, but unfortunately nested selects did not work on our SQL2000 site. I got an 'incorrect syntax' error.
I've decided to populate a temp table with each of the values in the original top 5 and join that table into the original query.

Aaah rats. It wasn't your SQL2000 server, it was my failure to test it. Sorry.

Try this, it should work:

Select TOP 1 * From (

Select TOP 3 * From (

Select TOP 5 * From syscolumns

Order By id, colid) a

Order By id,colid DESC

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Mohit (3/9/2009)
*smack* ... I knew I should have drank more coffee ...
>_> Sorry.

No worries, I do this all the time. Especially with ROW_NUMBER() which I love to use.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Heh... ya just gotta know what the next question on this will be... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PeterR-1037872 SSC Veteran Points: 282 More actions · Answer 11

Thanks RBarry, this version works well.

I've had to revise my thinking. It's good to have different options from which to choose.

This has been a rewarding first time experience of this Forum.

PeterR-1037872 SSC Veteran Points: 282 More actions · Answer 12

I have another question RBarry.

What do the two extra 'a' characters signify? I know that they make the script do what it should, which is fantastic, but I can't find what they actually do.

Instead of ploughing through more documentation, I thought it would be quicker to ask.

Thanks.

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 13

You tried to figure it out that counts worth alot :). Best way to learn, but sometimes it is easier to ask.

The "a" after the SQL statement are alising the statement

Like in select ...

SELECT 'John' AS Name

From SometimeTable AS TableAlias

Thanks....

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

That was the first question... now, let's see if the final question about this comes up. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)