grabbing first row in a mysql query only

Question

if i had a query such as

select * from tbl_foo where name = 'sarmen'

and this table has multiple instances of name = sarmen how can i virtually assign row numbers to each row without having to create a column that auto incriments? i have a reason for what im doing and dont need an auto_incrimented col in my example.

so if each row is assign a virtual row number through sql or maybe php i will be able to print out the first row or the last row anytime i need to.

thnx

Answer 1

To return only one row use LIMIT 1:

SELECT *
FROM tbl_foo
WHERE name = 'sarmen'
LIMIT 1

In doesn't makes sense to say 'first row' or 'last row' unless you have an ORDER BY clause. Assuming you add an ORDER BY clause then you can use LIMIT in the following ways:

• To get the first row use LIMIT 1.
• To get the 2nd row you can use limit with an offset: LIMIT 1, 1.
• To get the last row invert the order (change ASC to DESC or vice versa) then use LIMIT 1.

Answer 2

use the pseudocolumn ROWNUM

Answer 3

You didn't specify how the order is determined, but this will give you a rank value in MySQL:

SELECT t.*,
       @rownum := @rownum +1 AS rank
  FROM TBL_FOO t
  JOIN (SELECT @rownum := 0) r
 WHERE t.name = 'sarmen'

Then you can pick out what rows you want, based on the rank value.

Answer 4

You can get the total number of rows containing a specific name using:

SELECT COUNT(*) FROM tbl_foo WHERE name = 'sarmen'

Given the count, you can now get the nth row using:

SELECT * FROM tbl_foo WHERE name = 'sarmen' LIMIT (n - 1), 1

Where 1 <= n <= COUNT(*) from the first query.

Example:

getting the 3rd row

SELECT * FROM tbl_foo WHERE name = 'sarmen' LIMIT 2, 1