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

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

OK Jeff - Why is it necessary?

(Hope I got the question right)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

Heh... close... the next question will likely be "How can I parameterize the TOPs in SQL Server 2000?"

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

PeterR (3/11/2009)
OK Jeff - Why is it necessary?
(Hope I got the question right)

Since that wasn't the question that Jeff wanted, I'll answer this one, ...:D

They are necessary because all table sources in a FROM clause must have a name. Expressions have no "default" name the way a table, view or function does, so you have to give it a name using an alias (the only means left to give it a name).

Table sources in a FROM must have a name you can refer to it's returned columns unambiguously. True, there are many cases where you could argue that this is unneeded, but the syntax was written to anticipate the need.

[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 4

Jeff Moden (3/11/2009)
Heh... close... the next question will likely be "How can I parameterize the TOPs in SQL Server 2000?"

Well, dynamic SQL would be my first response.

[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: 1004313 More actions · Answer 5

RBarryYoung (3/11/2009)
PeterR (3/11/2009)
OK Jeff - Why is it necessary?
(Hope I got the question right)
Since that wasn't the question that Jeff wanted, I'll answer this one, ...:D
They are necessary because all table sources in a FROM clause must have a name. Expressions have no "default" name the way a table, view or function does, so you have to give it a name using an alias (the only means left to give it a name).
Table sources in a FROM must have a name you can refer to it's returned columns unambiguously. True, there are many cases where you could argue that this is unneeded, but the syntax was written to anticipate the need.

I don't believe that was the question... the question about "it" is why would someone want the 2nd and 3rd entries and what decides what the order would be. And I'm a bit disappointed in my prediction that someone would ask how to make it programmable.

Anyway, just having a little fun here. Thanks for playing along.:)

--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 6

Even though thanks was given earlier, before the additional interactions, I would like to reiterate:-

Thanks heaps guys for your solutions, explanations, advice and fun.

This won't be the last time I seek help from this forum.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Hmm, I guess we'll have to disagree on that one, Jeff. 🙂 It still looks to me like the OP was asking why it failed until I added the aliases on to it. An earlier post by the OP gave me the impression that they understood the TOP nesting trick once they saw it.

[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]