February 3, 2003 at 10:49 am
Hi all.
Is there a way to select an nth record from a table? If anyone has something similar, I'd appreciate if they share.
Thanks
February 3, 2003 at 1:22 pm
One way is to use two TOP statements:
USE Northwind
SELECT TOP 1 tablename.LastName
FROM
(Select TOP 20 LastName
from Employees
order by HireDate)
AS tablename
Everett Wilson
ewilson10@yahoo.com
February 21, 2003 at 3:14 am
Try this -
DECLARE @i INT
SET @i = 8
SELECT *, IDENTITY(INT,1,1) AS AutoId INTO #temp FROM Property
SELECT *
FROM #temp
WHERE AutoId = @i
DROP TABLE #temp
Hope this helps you.
February 21, 2003 at 3:46 am
There are several ways to accomplish however most require you have a single field you can use to order the data. And to get proper results it should be a unique field that is already the order of the data.
EWilson shows one, another is like so.
SELECT * FROM tblX OX WHERE (SELECT COUNT(*) + 1 FROM tblX IX WHERE IX.UniID < OX.UniID) = n
n representing the number of the record you want. Also assumes UniID is Unique an Clustered on.
The problem with this as with EWilsons (slightly less depending on several factors) is the larger the table the more costly and as I said before requires you have a field that already orders the table or in addition is Unique such as my example.
Now as for santoshnikam's that is also common but several issues I will point out are SELECT...INTO has been known to exibit locking issues so you sould use CREATE TABLE #temp instead and then INSERT #temp.
Also, if your table is extremely large width plus length or total number of records you can see issues there. If you have a Unique value for the record then I suggest do the same but have the Auto numbering INDENTITY column plus the data to make the unique key, select to insert those values from your main table then do an INNNER JOIN to lin up and conserve a bit of resources and potentially time.
Another factor to consider is if the table has no Clustered index there is no guarantee you will get the same results each time you run.
And finally the other option you can use is a cursor to pull either the whole table or unique value and FETCH to the position you want.
Overall test all possible solutions and see which gives you the best consitant performance without major server overhead.
February 24, 2003 at 1:43 pm
Part of the problem is MSSQL's limit on the TOP clause--you cannot specify a variable. However, if you do know which record you want, you can use MSSQL2K's new table variable instead of using a temporary table. The table variable is stored in memory where the temporary table may be written to disk.
declare @tbl table ( unikey int )
insert into @tbl select top 20 uniquecolumn from tablename
set rowcount 19
delete from @tbl
set rowcount 0
select * from tablename
where uniquecolumn in (select top 1 unikey from @tbl)
February 24, 2003 at 5:50 pm
Interesting option there Greg. My only concern with Table variables would be say you need the middle value or some other variant on an extremely large table. You would then likely cause yourself resource issues.
It would be nice if MS would give a way to find the nth value as @@rowcol = 5 or something similar.
February 25, 2003 at 7:30 am
Just wanted to correct EWILSON10's two query option a little. You need to sort each query in different orders (inner one asc, outer one decending)
USE Northwind
SELECT TOP 1 tablename.LastName
FROM
(Select TOP 5 LastName , hiredate
from Employees
order by HireDate)
AS tablename
order by Hiredate Desc
Change the number from 20 to 5, since there are only 9 records total.
remember to keep in mind the this Antares686 said
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 25, 2003 at 2:56 pm
Antares' mention of FETCH seems worth greater comment.
Barring large overhead due to the cursor, this seems the cleanest.
Question though, which would be less of a burden, a temporary table or a cursor?
I threw together the following based on a couple of scripts from BOL.
USE pubs
GO
DECLARE @var int
set @var = 3
DECLARE authors_cursor SCROLL CURSOR FOR
SELECT au_lname FROM authors
ORDER BY au_lname
OPEN authors_cursor
FETCH RELATIVE @var FROM authors_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH RELATIVE @var FROM authors_cursor
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
One comment regarding variables with the TOP statement.
Keep in mind variables can be used if coming from a built string,
such as from a web server, or used with the exec command.
Everett Wilson
ewilson10@yahoo.com
February 26, 2003 at 5:43 pm
quote:
Another factor to consider is if the table has no Clustered index there is no guarantee you will get the same results each time you run.
Even with a clustered index, there is no guarantee that the data will be selected in the proper order. Empirically, the data is often returned in this "nice" order but don't rely on this for all cases.
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 26, 2003 at 5:44 pm
quote:
Another factor to consider is if the table has no Clustered index there is no guarantee you will get the same results each time you run.
Even with a clustered index, there is no guarantee that the data will be selected in the proper order. Empirically, the data is often returned in this "nice" order but don't rely on this for all cases.
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply