Page 1 of 1

[SOLVED] MySQL position of a row

Posted: Sat Jul 29, 2006 5:04 am
by Ollie Saunders
Here's an example table:

Code: Select all

CREATE TABLE Example (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
                   PRIMARY KEY(id),
    someNum        INT UNSIGNED NOT NULL 
                   UNIQUE INDEX(someNum)
);
I want to find out how many rows deep row this row is:

Code: Select all

SELECT id FROM Example WHERE someNum = x
x could be 10 000 for example.
How many rows are above this when it is ORDER BY id DESC?

Bare in mind id may not be contigious and someNum is random.

Posted: Sat Jul 29, 2006 6:00 am
by Benjamin

Code: Select all

SELECT `id` FROM `table` WHERE `someNum` = 'x'; -- pull the id
SELECT COUNT(*) AS RecordCount FROM `table` WHERE `id` >= 'ID'; -- count all larger id's

Posted: Sat Jul 29, 2006 6:43 am
by Ollie Saunders
Great, thanks astions.