December 19, 2005 at 12:17 pm
Hello gang! I know this is very simple for you SQL gods, but me here is breaking my head.
My boss came to me few minutes ago, asking for every tenth record from our contact book up to 7,000.
How can I select those records from a SQL query?
Please help!
Thanks.
December 19, 2005 at 12:33 pm
Assuming you have an integer ID (Identity 1,1) use a MOD type operation. If you don't have an ID IDENTITY(1,1) then create a temp table with one and insert your records. Then select as shown. The selection will use the first, eleventh, twentyfirst, etc.
SELECT TOP 7000 * FROM YourTable WHERE ((ID+9)/10)=0
OR
SELECT * FROM YourTable WHERE ID <=7000 AND ((ID+9)/10)=0
December 19, 2005 at 1:15 pm
Thank you! I tried it but it returned no records.
What could be wrong?:
December 19, 2005 at 1:31 pm
>>What could be wrong?
Which SQL did you try ? What do the Row ID's in your table start at ?
December 19, 2005 at 1:36 pm
I tried both suggested queries, and my row id starts at 1 to 137,000.
December 19, 2005 at 1:44 pm
There is an error in the SQL. Use the '%' operator for modulus:
SELECT TOP 7000 * FROM YourTable WHERE (ID % 10) = 1
December 19, 2005 at 1:45 pm
OK you ought to use the MODULO operator instead of the more complex math:
select * from sysobjects where id % 10 = 0
But the caveat is: IF you have a sequential ID without gaps, THEN use that method.
HOWEVER, IF you have gaps AND you have a simple ID, THEN:
select IDENTITY ( INT, 1, 1 ) AS sid, ID INTO #t FROM basetable
then join to main with mod function:
SELECT ...
FROM basetable a
JOIN #t b ON b.ID = a.ID
WHERE b.sid % 10 = 0
December 19, 2005 at 6:01 pm
John, you rock!
that's exclatly what my boss wanted, we do have gaps in that table.
thanks again, and thank you all of you that contribute your help.
Many thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply