March 9, 2009 at 9:34 pm
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.
March 9, 2009 at 10:59 pm
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)
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]
March 9, 2009 at 11:05 pm
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.
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]
March 9, 2009 at 11:27 pm
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)
March 9, 2009 at 11:39 pm
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]
March 9, 2009 at 11:45 pm
*smack* ... I knew I should have drank more coffee ...
>_> Sorry.
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]
March 9, 2009 at 11:50 pm
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"
March 10, 2009 at 5:26 pm
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.
March 10, 2009 at 7:16 pm
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) 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]
March 10, 2009 at 7:18 pm
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]
March 10, 2009 at 8:28 pm
Heh... ya just gotta know what the next question on this will be... 😉
--Jeff Moden
March 10, 2009 at 9:50 pm
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.
March 11, 2009 at 4:14 pm
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.
March 11, 2009 at 4:20 pm
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....
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]
March 11, 2009 at 7:25 pm
That was the first question... now, let's see if the final question about this comes up. 😉
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply