March 23, 2004 at 12:53 pm
I had someone recently ask how to get the top 12th record in a table that doesn't have an autonumber field. I've tested this and it works but I'm not sure how processor intensive it would be if you were querying allot of records.
--Select fields for output. You can put as many as you want
SELECT chCustomer, chLocation
FROM TB_CustomerMaster
--Right here you can select any field for the criteria
WHERE (chCustomer IN
--You only want 1 record to return so TOP 1
(SELECT TOP 1 chCustomer
FROM TB_CustomerMaster
WHERE chCustomer IN
--Now get Top N records and the last record is the one we want
--Say you want the 12th record in a table
(SELECT TOP 12 chCustomer
FROM TB_CustomerMaster
--Here's where it work's
--Get TOP 11... 1 less then what we want
--Since we did 12 up top and 11 here the difference will give you
--the last record
WHERE chCustomer NOT IN
(SELECT TOP 11 chCustomer
FROM TB_CustomerMaster))))
March 23, 2004 at 1:29 pm
Not very performant I think. To speed it up a bit, you can put all the code into a stored procedure.
Another thing you can try is this:
CREATE TABLE #Top
(Pos INT IDENTITY (1,1) NOT NULL,
chCustomer VARCHAR(50), -- this should be the same data types you use
chLocation VARCHAR(50)) -- this should be the same data types you use
INSERT INTO #Top
(chCustomer, chLocation)
SELECT chCustomer, chLocation
FROM TB_CustomerMaster
SELECT chCustomer, chLocation
FROM #Top
WHERE Pos = 19
DROP TABLE #Top
March 23, 2004 at 2:06 pm
I have also done it before using temp tables with a identity field. The purpose of the post above was someone needed it in a straight select statement.
Thanks for the reply!
March 23, 2004 at 3:38 pm
Depending on the data size it can get intensive. But to add to the temp table idea you can also do this to conserve a bit.
INSERT INTO #Top
(chCustomer, chLocation)
SELECT TOP 19 chCustomer, chLocation
FROM TB_CustomerMaster
SELECT chCustomer, chLocation
FROM #Top
WHERE Pos = 19
But the above requires dynamic SQL to meet many needs might also try this
SET @@rowcount @pos
INSERT INTO #Top
(chCustomer, chLocation)
SELECT chCustomer, chLocation
FROM TB_CustomerMaster
SET @@rowcount 0
SELECT chCustomer, chLocation
FROM #Top
WHERE Pos = @pos
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply