Member Avatar for jacksantho

Hi,
Please help me out .

I need to retrieve the records from 11th row onwards using select query. Please help me out . Its urgent. Thanks

  • 3 Contributors
  • 4 Replies
  • 350 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

SQL Server 2000 has no ROW_NUMBER() or OFFSET, so you must emulate “skip first N rows” with other tricks. ’s correlated-count technique will work but can be very slow on large tables; ’s ROW_NUMBER idea is the cleanest where available, but it’s not supported on SQL‑2000.

A simple, common pattern is to exclude the first N rows using TOP on the primary key (safe if the PK is NOT NULL and you include a deterministic ORDER BY):

SELECT *
FROM Employee
WHERE EmpID NOT IN (
  SELECT TOP 10 EmpID
  FROM Employee
  ORDER BY LastName, FirstName
)
ORDER BY LastName, FirstName;

If you need a more robust row-number (or want to avoid NOT IN/NULL pitfalls), materialize the ordered result into a temp table with an IDENTITY column and then filter by that generated row number:

CREATE TABLE #OrderedEmp (
  RowNum INT IDENTITY(1,1) PRIMARY KEY,
  EmpID INT, FirstName VARCHAR(50), LastName VARCHAR(50)
);

INSERT INTO #OrderedEmp (EmpID, FirstName, LastName)
SELECT EmpID, FirstName, LastName
FROM Employee
ORDER BY LastName, FirstName;

SELECT *
FROM #OrderedEmp
WHERE RowNum >= 11
ORDER BY RowNum;

DROP TABLE #OrderedEmp;

Notes and cautions:

  • Always specify an ORDER BY that makes the row ordering deterministic (include the PK to break ties). Without that, “11th row” is undefined.
  • NOT IN will fail if the subquery can return NULLs; use NOT EXISTS or the temp-table approach to avoid that.
  • Both approaches require sorting—add an index on the ORDER BY columns to improve performance.
  • For paging (rows 11–20), use the same TOP/NOT IN pattern but select TOP 10 in the outer query after skipping TOP 10 in the subquery.

These options are what you’ll typically use on SQL‑2000; if you can upgrade to 2005+ you can replace them with ROW_NUMBER() (or OFFSET/FETCH in later versions) for simpler paging.

Recommended Answers

All 4 Replies

Member Avatar for jbisono

This is what I would do. Imagine you have table "Employee" with this two fields FirstName, LastName then you can do this.

SELECT FirstName, LastName, RowNumber
FROM(
SELECT Emp.FirstName, Emp.LastName, (SELECT COUNT(*) FROM Employee AS Emp2 WHERE (Emp2.LastName < Emp.LastName)) AS RowNumber
FROM Employee AS Emp) AS derivedTable
WHERE RowNumber >= 11
ORDER BY RowNumber
Edited by jbisono because: n/a
Member Avatar for adam_k 
select * from (select FirstName, LastName, ROW_NUMBER() over (order by LastName,FirstName) as 'Row' ) a 
where Row >=11

Pretty much the same thing as jbisono's query.

Member Avatar for jbisono

The problem with your statement adam is that ROW_NUMBER() is not supported by SQL 2000, but besides that I like your statement better :-) ....

Member Avatar for adam_k

Missed that requirement, you are right.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.