Select between recno 100 and 250 4

[konLao]

Hi all,

How can I select record 100 to 250? (SQL Select)

Thank you

 

[scriverb]

Does the recordset already have a record number that can be used? If so use the following:

Select A.* 
FROM [i]yourtablename[/i] as A 
WHERE A.[[i]recordnumber[/i]] >= 100 and A.[[i]recordnumber[/i]] <= 250;

Post back if you don't have a recordnumber field.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[konLao]

scriverb,

Thank you very much for responding so quickly. You are right, I don't have recordnumber field. Do I have to use identity function to to extract record number inot saperated table? Please show me the sample code.

Thank you,

Konlao

 

[scriverb]

Try using this SQL:

SELECT Count(*) AS Count, A.* 
FROM [i][red]yourtablename[/red][/i] AS A, [i][red]yourtablename[/red][/i] AS B 
WHERE (((A.[i][blue]unique_identifier[/blue][/i])<=B.[i][blue]unique_identifier[/blue][/i]))
GROUP BY A.[i][blue]unique_identifier[/blue][/i] 
HAVING (((Count(*))>=100 And (Count(*))<=250))
ORDER BY Count(*);

The tables)red) are both the same table. The unique_identifier field is just that. Your recordsource has a specifc sort order. This sort order is identified by a field or fields. We can use those fields as the unique identifier to select the correct records.

Post back with question. An easy way to do this is create a new field in your table as an AutoNumber. Then use that as the Unique_Identifier field. The above code will then number the select records and select 150 through 250.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[konLao]

scriverb,
I'm using MS-SQL 2k. My table doesn't have unique id built in. How can I created a uniqueidentifier field and add autoincrement value to this newly created field?

Thank ,you

 

[scriverb]

It doesn't have to be an autonumber field. I was just suggesting that. What is the order of your table? The order of the table will identify the fields that we can use to number the rows in the query. It could be CaseNumber, or EmpID, Name, or a combination of two or more fields. We can modify the query to create the rownumbers as long as I know the order and fields.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[OhioSteve]

You can put the phrase "top x" immediately after the word "select". Then the query will just select the top x records. Using this phrase, there is an awkward way to use that to select records x to y. Consider this example~

Query A:
SELECT TOP 2
myTable.myField
FROM myTable;

Query B:
SELECT TOP 1
myTable.myField
FROM myTable;

Query C:
SELECT [Query A].myField,
[Query B].myField
FROM [Query A]
LEFT JOIN [Query B] ON [Query A].myField = [Query B].myField
WHERE ((([Query B].myField) Is Null));

Query C selects only the second record in the table.

This technique is clumsy, but it does retrieve records x to y without using an autonumber field.

 

[OhioSteve]

I think in Access you must insert the phrase "top x" in the actual sql. Its like a union query, you can't do it graphically.

 

[PHV]

Have you tried something like this ?
SELECT TOP 150 B.* FROM
(SELECT TOP 250 * FROM theTable
ORDER BY SequenceField) AS B
ORDER BY B.SequenceField DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scriverb]

PHV: Nice and clean. I like that, so have a star. My code does work but you just have to have the identifiers correct.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[OhioSteve]

Yes! PHV that is brilliant. It does the same thing as my sql, but it is much more elegant.

 

[konLao]

Thank all of you. This is the first time I posted a question on "Microsoft: Access Queries and JET SQL" Forum and the answer I get is unbelievable. You guys are great. You deserve star - all of you.

Keep up the good help... you will see me again.

Thank you again.

KonLao