Random Query 1

[Mbroad]

Hi--I need to develop a query which will do the following:

Select *
from table1

picking every nth record (sequentially) and writing to output table


I appreciate your assistance.
Thanks,
MB

Government Coder Mark

 

[SQLDenis]

paging?

take a look at

http://www.aspfaq.com/show.asp?id=2120

scroll down for the SQL only solutions

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Tell us what is primary key and desired ORDER BY.

Also: SQL2000 or SQL2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Mbroad]

This is a file for State of Michigan. The order will be drivers-license number and is MS SQL2000.
Thanks,
MB

Government Coder Mark

 

[vongrunt]

Assuming license number is unique (as it should be?)...

select identity(int, 1, 1) as seq, dlnumber
into #blah
from myTable
order by dlnumber
option (maxdop 1)

-- get every 5th record
select A.*
from myTable A
inner join #blah B on A.dlnumber = B.dlnumber
where  B.seq % 5 = 0

drop table #blah

Btw this code is OK only for data sampling or something, not frequent use or business logic - partially because SQL2000 ranking svcks.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Mbroad]

Thank you very much for your post. I have not used the IDENTITY function before (novice SQL programmer), so I am trying to fit my tables into your code. I have "fitted" my table/column names as below. See if this makes sense (SID is the state-id column from the Secretary of State table):
-----------------------------------------------------------
select identity(int, 1, 1) as seq, SOS_TABLE.SID
into SOS_OUTPUT
from SOS_TABLE
order by SOS_TABLE.SID
option (maxdop 1)
===========================================================
**I'm not sure about this section!! What table goes into table "B"?? I wish to select every 5TH record from table A and write to SOS_OUTPUT. Please explain**

-- get every 5th record
select A.*
from SOS_TABLE A
inner join #blah B on A.dlnumber = B.dlnumber
where B.seq % 5 = 0

drop table #blah
------------------------------------------------------------
Thanks very much,
MB


Government Coder Mark

 

[vongrunt]

Er... not directly into SOS_TABLE.

Idea is rather simple but takes two steps:

1. select and rank primary/unique keys from SOS_TABLE into temp table (#blah). First row will get seq=1, second seq=2 etc.
2. Join that temp table back on primary key and use % in WHERE clause - query will return every 5th row:

select identity(int, 1, 1) as seq, SOS_TABLE.SID
into [!]#blah[/!]
from SOS_TABLE
order by SOS_TABLE.SID
option (maxdop 1)

select A.*
from SOS_TABLE A
inner join #blah B on [!]A.SID = B.SID[/!]
where  B.seq % 5 = 0

This is SELECT. Wanna insert these rows into SOS_OUTPUT table, simply modify last query to do INSERT INTO (if output table exists) or SELECT INTO (if table does not exist).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]