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

User · Answer

To return only one row use LIMIT 1   SELECT   FROM tbl foo WHERE name    sarmen  LIMIT 1   It doesn t make 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

User · Answer

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  lt   n  lt   COUNT    from the first query   Example   getting the 3rd row  SELECT   FROM tbl foo WHERE name    sarmen  LIMIT 2  1

User · Answer

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

[mysql] grabbing first row in a mysql query only

Examples related to mysql