List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 1 2001 1:02am
Subject:Re: Finding row # for a row.
View as plain text  
At 3:16 PM -0800 10/31/01, Mike wrote:
>Okay, say I have a database with 10,000 entries in it. All entries 
>have a unique id, but that ID is not exactly sequential (in fact, 
>it's a 128-bit checksum).
>
>Now, given that ID, I need to find out what row # in the grand 
>scheme that it is.

There's no such concept.  Relational tables are sets, with no notion
of row number.

I'm curious what such a number would give you that the ID won't.
You certainly wouldn't be able to use it later to identify the record.

>
>Let's take this test statement:
>
>SELECT * FROM auction ORDER BY lotnum ASC
>
>Is there any way I can add something to that, that might return what 
>row# a certain item is, using a WHERE condition?

You can use this trick, though perhaps it's not what you want:

SET @n = 0;
SELECT @n:=@n+1 AS rownum, auction.* FROM auction ORDER BY lotnum ASC;

Requires MySQL 3.23.6 or later.


>
>Mike


Thread
Finding row # for a row.Mike1 Nov
  • Re: Finding row # for a row.Paul DuBois1 Nov