Page 1 of 1

counting number of rows

Posted: Tue Jun 07, 2005 4:25 am
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

Posted: Tue Jun 07, 2005 5:10 am
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'

Posted: Tue Jun 07, 2005 11:11 pm
by hongco
thanks Tim, that was very helpful :)