counting number of rows

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

counting number of rows

Post by hongco »

if on a table in mysql database contains these rows:

id | fname | lname
1 | Kenedy| John
2 | Tom | Brady
13 | Kim | Brady
20 | Marie | Presly

if fname = Tom is given,
Is there any function in mysql to allow us find number of records before the record containing Tom?

Thanks
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you have support for subqueries

Code: Select all

SELECT COUNT(*)
FROM table
WHERE id < ( SELECT id 
             FROM table
             WHERE fname='Tom' )
Or if you don't have support for subqueries

Code: Select all

SELECT COUNT(*)
FROM table AS t
INNER JOIN table AS t2 USING id
WHERE t.id < t2.id
AND t2.fname = 'Tom'
hongco
Forum Contributor
Posts: 186
Joined: Sun Feb 20, 2005 2:49 pm

Post by hongco »

thanks Tim, that was very helpful :)
Post Reply