Query to return record at Nth physical location in table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Office 2003 on Windows XP.

Can some one please post example SQL that would return, let's say, the 5th
record in a table something like the following:

Dim lX as Long
lX = 5
SELECT * FROM tbl1 WHERE [RowNumber] = " & lX & ";"

The above obviously doesn't work. Please note I want the actual physical
record position in the table and not an ID number equivalent.

Thanks much in advance.
 
You need to rethink your design if you are going to depend on a row's
physical location within a table.

Dorian
 
Dorian,

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

Does anyone else have an answer?

mscertified said:
You need to rethink your design if you are going to depend on a row's
physical location within a table.

Dorian

quartz said:
I am using Office 2003 on Windows XP.

Can some one please post example SQL that would return, let's say, the 5th
record in a table something like the following:

Dim lX as Long
lX = 5
SELECT * FROM tbl1 WHERE [RowNumber] = " & lX & ";"

The above obviously doesn't work. Please note I want the actual physical
record position in the table and not an ID number equivalent.

Thanks much in advance.
Click to expand...
Click to expand...
 
Yes, You need to rethink your design if you are going to depend on a row's
physical location within a table.

A table is an unordered collection of records. What is row 5 today may not
be row 5 tomorrow. If you want to visualise your data as a consistently
ordered set then you will have to put a sequence number in it, or a date, or
any other sequence identifier that you can think of. Normally we order sets
by using a query with a sort order in it.

Tell us why you think you want to see a records sequence number and we may
be able to help you further.


quartz said:
Dorian,

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

Does anyone else have an answer?

mscertified said:
You need to rethink your design if you are going to depend on a row's
physical location within a table.

Dorian

quartz said:
I am using Office 2003 on Windows XP.

Can some one please post example SQL that would return, let's say, the
5th
record in a table something like the following:

Dim lX as Long
lX = 5
SELECT * FROM tbl1 WHERE [RowNumber] = " & lX & ";"

The above obviously doesn't work. Please note I want the actual
physical
record position in the table and not an ID number equivalent.

Thanks much in advance.
Click to expand...
Click to expand...
Click to expand...
 
Can some one please post example SQL that would return, let's say, the 5th
record in a table
Click to expand...

No, because tables do not have row numbers. That's like asking for the
fifth potato in a bag of potatoes - the question is meaningless.

John W. Vinson[MVP]
 
Okay, SO CAN ANYONE SUPPLY ME WITH THE SQL I AM ASKING FOR?

I appreciate the fact that you guys only think about databases used in
conventional ways, so you only think normalized database right? Try thinking
outside the box for a change. You don't have a clue what I am trying to do,
or why...but it really doesn't and shouldn't matter to you; nor do I really
want to sit up half the night explaining it - and actually, I not at liberty
to.

Why all the resistance? If you don't have an answer or if it can't be done
in Access, then please just say so. When I post on any other forum, I simply
get answers. I asked for some help on SQL, which I thought was what this
forum was for...

Regards to all who read my post; I hope I didn't get anyone too riled up and
that I'm still welcome in this NG; but it's late and I've been working a long
time.

Good night guys.
 
If tables don't work that way, SQL will not work that way.

If you look though these newsgroups and sites referred to in them you will
find plenty of "Out of the Box" thinking. The reason why I was asking "Why"
was so I could try and go through the right wall of the box if that was
required. Assembler and bit twiddling can get out of most computer "boxes".
Too often I have twiddled only to find that the answer is "in the box" after
all. You can see examples of that throughout these newsgroups too. "Ooh, can
it do that?"

If it will make you really happy search the Access newsgroups for "Custom
autonumber", create a query that applies such to a SELECT * FROM TABLE;
query, which is doing no sorting, and if you believe that the record
numbered 5 is the fifth record, then you can go to bed happy.
 
quartz said:
Dorian,

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

Does anyone else have an answer?
Click to expand...

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.
 
Okay, SO CAN ANYONE SUPPLY ME WITH THE SQL I AM ASKING FOR?
Click to expand...

No, because what you are asking for does not exist.

There are no row numbers in Access tables. They simply do not exist;
no SQL query can create them de novo.

Note that if you were to Compact your database, the order of records
within the table will most likely change; what was the 5th record
before might be the 317th after the compaction.


John W. Vinson[MVP]
 
Okay, so if I am reading between the lines correctly, it can't be done?

I understand everything everyone has posted; obviously you are all correct
about how the DB functions and I am counting on that; and I am definitely not
challenging anyone's expertise as I'm sure you could stamp me into the ground
on a debate about DB functionality and structure.

You see, what you've explained is EXACTLY what I need. If, physically
looking at the table from top to bottom at a moment in time, record XYZ
happens to be the fifth one in the heap, right at this very moment, then that
is EXACTLY the one I need. It doesn't matter to me, nor my application, where
that record is even one second from now. I won't always need the fifth, it
might be the 18th, or what ever.

If there are at least 5 records in a table, then at any moment in time,
there must be a 5th record from the top, so it must exist; it has to. I just
need to get that particular item - at a particular time.

The point is, I already understand HOW it works. That is not what I want or
need. I cannot use an AutoNumber, because, for the very reasons everyone has
pointed out, that would not return the correct item in the stack at that
moment.

My work around is, for example to get the fifth: "SELECT TOP 5 * FROM ..."
then move the cursor to the last record in the Recordset to access the fifth
item. I'm presuming that would do it so long as I don't use an ORDER BY.
Unless someone has a better suggestion?

Thanks again for all the assistance and time; also, sorry for any confusion
this has caused.
 
The only time I have addressed this problem before was trying to recover
information from a corrupt database (we did not succeed) and to recover
information from a secured database after the previous developer had
allegedly "died". (we failed).

I was, however, learning stuff along the way, and it was possible with
enough time devoted that I would have got results. I was trying to determine
how honourable were your motives, and how much time and effort the hoped for
result was worth. I could not tell you how to reach your destination, but I
was trying to warn you about some wrong turns.

There is at least one Access data recovery company that could possibly help
if the motives and the money were right. Given all of the posts about
corrupt databases and lost passwords you might start wondering why there are
not more such. It is "just" a case of determining the file format.

To make your day search the newsgroups for the posts on "my TOP query
returns too many records" and similar themes, though I don't hink you will
hit that one..
 
Would you happen to be looking for some random records? Looking for what
happens to be the 5 physical record at a moment in time could certainly
produce random results each time that you run the sql in a multi-user
environment or if you do compact on close. There is a Random function inside
the box.
 
quartz said:
Okay, so if I am reading between the lines correctly, it can't be done?

[...]

It doesn't matter to me, nor my application, where that record is even
one second from now. I won't always need the fifth, it might be the
18th, or what ever.

[...]

Thanks again for all the assistance and time; also, sorry for any confusion
this has caused.
Click to expand...

quartz,

Did you read all the way to the bottom of my post on the other
branch of this thread?

Just use three queries, one to create an artificial ascending number
sequence, one to create a repeating artificial ascending number
sequence, and one to select the 5th repeating number.


Sincerely,

Chris O.
 
My work around is, for example to get the fifth: "SELECT TOP 5 * FROM ..."
then move the cursor to the last record in the Recordset to access the fifth
item. I'm presuming that would do it so long as I don't use an ORDER BY.
Unless someone has a better suggestion?
Click to expand...

You have no guarantee that the TOP 5 query will return records in the
same order as the table disk storage.

I'd suggest doing it all in VBA - open a table-type recordset and use
MOVE to go to the desired record. I don't believe there is any
*reliable* method in a SQL query.

John W. Vinson[MVP]
 
Back
Top