[SOLVED] MySQL position of a row

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

[SOLVED] MySQL position of a row

Post 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.
Last edited by Ollie Saunders on Sat Jul 29, 2006 6:43 am, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Great, thanks astions.
Post Reply