Limit Query - MS SQL

[jrubins]

I'm trying to run a query to limit the number of records returned. I have a table named "Patient" with "PatientID" as one of the fields. I run this SQL statement and get a syntax error. Any suggestions?

SELECT TOP 25 *
FROM Patient
WHERE key NOT IN (
SELECT TOP 0 [Patient]![PatientID]
FROM Patient
ORDER BY [Patient]![PatientID]);

 

[Banana]

Pretty sure TOP 0 isn't valid. I'd just omit the TOP 0. But then, the SQL doesn't make sense.

What are you trying to do with this query?

 

[jrubins]

I have about 2000 records and I want to view only 200 at a time (so i can print off a list of 200 at a time). This code worked:

SELECT TOP 200 *
FROM Patient
WHERE (((Patient.StudyStatusID)=3))
ORDER BY Patient.[PatientID];

But that only gets me the first 200, and I don't know how to get the next 200.

 

[Banana]

You would need a parameter query:

SELECT TOP 200 * 
FROM Patient
WHERE ((Patient.StudyStatusID) = 3)) AND PatientID > @PID
ORDER BY Patient.PatientID;

For first 200, input 0 for @PID. For the next 200, look at the ID of the 200th record and input that and so forth.

Alternatively, if you're using SQL Server 2005, then google "ROW_NUMBER()" function which will build a ranked table and you can then page through the virtual table.

 

[jrubins]

Thanks a lot! That worked perfectly.

 

[jrubins]

Is there a way to have the query prompt the user for the range (e.g. 1-200, 201-400, etc.) instead of having to go into the SQL everytime and update it?

 

[Banana]

The trouble is that we don't know what the start point and end point is.

What you could, do is have a separate query to tell you what the starting point is. So have a unbound dialog form for the user to enter. Let's say the user wants range 200-400, then you would issue this query:

SELECT PatientID
FROM Patient p
WHERE @PID = (SELECT COUNT(PatientID) FROM Patient WHERE Patient.PatientID >= p.PatiendID)
ORDER BY PatientID;

But be aware due to the correlated subquery, the performance can be abysmal for a large number. ROW_NUMBER() may be a better suggestion, and you could create a temporary table and reference that table without the trouble of counting & figuring out the position just in time.

 

[jrubins]

Ok, I have considered ROW NUMBER() but wasn't 100% about it. But I'll probably try that out now. Thanks again.