May 5, 2009 at 12:58 pm
I have the following data output:
1
2
3
4
5
6
7
8
9
10
I need to run the following code:
SELECT TOP2
FROM data above
It Returns:
1
2
How can i make it return?????
3
4
In mysql i can use a LIMIT to do this. Does anyone have any ideas? I Need this for SQL.
May 5, 2009 at 1:33 pm
In SQL 2000, you pretty much have to do this in steps. Select the top 4, then select the top 2 of that but with reversed ordering, then select from that. In SQL 2005, you could do this with the row_number() function much more easily.
In 2000, it might look like:
-- Build the test data
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
--
CREATE TABLE #T (Number INT) ;
--
INSERT INTO
#T (Number)
SELECT TOP 10
Number
FROM
dbo.Numbers ;-- I have a Numbers table with numbers from 1 to 10,000
--
-- Query the data
SELECT
Number
FROM
(SELECT TOP 2
Number
FROM
(SELECT TOP 4
Number
FROM
#T
ORDER BY
Number) Top4
ORDER BY
Number DESC) Bottom2
ORDER BY
Number ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2009 at 1:44 pm
Really no great solution, but this will work....
May 5, 2009 at 1:49 pm
Thanks for your help....that seems to do just what I wanted.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply