Re: Query to return record at Nth physical location in table

Tech-Archive recommends: Speed Up your PC by fixing your registry


"quartz" <quartz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:15DD8372-88D7-45E5-91D0-900635610FC2@xxxxxxxxxxxxxxxx
Dorian,

Thanks for your post. Then I assume you can't help.

Does anyone else have an answer?


Dorian,

There is no *good* answer to your question.

Sets (i.e. tables), as David Cox mentioned, are unordered.

Using a query to locate every Nth row in a table isn't a question
SQL is meant to answer.

If you have a compelling reason to select every Nth row, you may
consider iterating through a recordset using VBA, but without an
artificial sequence to depend upon in one column (also as
mentioned), even that may be different at any time.


Also, here is an example of how to create an artificial repeating
ascending number sequence.

You can can run a new query against the query below with "WHERE
NumberSeq = <your Nth value>".

(Note that this also depends on the ascending sequence in TitleID to
work at all.)


CREATE TABLE Titles
(TitleID AUTOINCREMENT
,TitleName TEXT(5)
,CONSTRAINT pk_Titles
PRIMARY KEY (TitleID)
)


Sample Data:

1, Jim
2, Jan
3, John
4, Al
5, Frank
6, Sam
7, Kate
8, Sally
9, Susie
10, Amy
11, Kevin
12, Phil


Query:

SELECT T1.TitleName
,(SELECT SWITCH(COUNT(T1.TitleID) MOD 3 = 1, 1
,COUNT(T1.TitleID) MOD 3 = 2, 2
,COUNT(T1.TitleID) MOD 3 = 0, 3)
FROM Titles AS T01
WHERE T01.TitleID <= T1.TitleID)
AS NumberSeq
FROM Titles AS T1


Results:

TitleName, NumberSeq

Jim, 1
Jan, 2
John, 3
Al, 1
Frank, 2
Sam, 3
Kate, 1
Sally, 2
Susie, 3
Amy, 1
Kevin, 2
Phil, 3



Sincerely,

Chris O.


.

Relevant Pages

  • Re: Spin calling John 100% identity (Human & Gekko)
    ... Unfortunately there are no other sequences to use in checking the accuracy of that EST, i.e. no Gekko atpase sequences, and the study the sequence comes from is unpublished. ... Query 4427 ... Sbjct 6 ... TCTGACATGGGGCCACCCCACAGGTCAGAGTGGTGGTAGAACCCCTTCAGGACTCCCAGC 245 ...
    (talk.origins)
  • Re: Working with many-to-many relationships
    ... You can now use the function in a query, just like the built-in functions such as Trimor Left. ... Allen Browne - Microsoft MVP. ... it would be best to get it working in a query, and then base the report on ... >>> The titles are in a table, the authors are in a table ...
    (microsoft.public.access.reports)
  • Re: Group By Sequential Records
    ... Is there any way to speed up the query? ... in each "sequence" where Group and AltID are the same and ID numbers are ... remains the same throughout each "sequence". ... FROM AddrCent AS Sub ...
    (microsoft.public.access.queries)
  • Re: Doubling the order
    ... The early SQLs were based on existing file systems. ... relational database and it is not the best one. ... the gap in the sequence is not filled in and the sequence ... Since a query result is a table, and a table is a set which has no ...
    (microsoft.public.sqlserver.programming)
  • Re: Doubling the order
    ... 1.PRODUCTID) - primary Key ... > data model or any data integrity. ... the gap in the sequence is not filled in and the sequence ... > Since a query result is a table, and a table is a set which has no ...
    (microsoft.public.sqlserver.programming)