February 21, 2006 at 4:05 am
February 22, 2006 at 5:47 am
Without an "order by" in the query, how are you defining the top three records? You are presumably getting the top three in order of the fields that make up the clustered index on the table; assuming you know what these are you can specify them in the query:
If your query to find the three most expensive books is
SELECT TOP 3 * FROM BOOKS ORDER BY PRICE DESC,
then the 3 cheapest are
SELECT TOP 3 * FROM BOOKS ORDER BY PRICE ASC.
HTH,
Gavin Campbell
February 23, 2006 at 1:37 am
Insert your records in a temporary table using an autoincrement column as the key (let say "lineno"). After doing so, your @@rowcount variable will be affected and you will be able to retrieve the temporary table like this
Insert into #TableA (fielda, fieldb)
select tab1_fielda, tab1_fieldb
where ......
.
.
.
Select @mycount = @@rowcount
Select .....
From #tableA
Where lineno between (@mycount -3) and @mycount
Be sure to index properly the lineno column after inserting records in the table in order to get and index or a clustered index scan for this query on large volume. I mentionned After for you not to pay for index reorg. at execution time for each inserted row
Sylvain Cartier
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply