MySQL Forums
Forum List  »  Newbie

Re: Getting to row number
Posted by: Russell Dyer
Date: July 28, 2005 01:29AM

You could create a user variable and then use it to keep count of the rows. To do this you would use the SET statement to create the variable. You would then use the := operator to increment the variable's value.

SET @row = 0;

SELECT @row := @row + 1 AS Row, col1
FROM table1;

This will display all rows found. You will have to look through the results manually to find the row for which you're looking to determine the row number for it.

If you add a WHERE clause that filters out all rows but one, it will only display one row and the value of @row will be 1. You didn't say which version of MySQL you're using. If you're using version 4.0 or higher of MySQL, you can take advantage of subqueries and generate a derived table and apply the WHERE clause to it. You would do that like this:

SELECT Row, col_a
FROM (SELECT @row := @row + 1 AS Row, col1 AS col_a FROM table1) As derived1
WHERE col_a = 'text';

The sub-query will generate a results set (a derived table) for all of the rows in table1 with a row count (Row). The main query will only display the rows from the derived table that meet the WHERE clause. This will not effect the numbering of rows in the derived table, though. By the way, every time you run this kind of statement, you'll have to rerun the SET statement to set the user variable back to 0. You may also want to add ORDER BY clause in the sub-query to make the results more orderly.

Russell Dyer

Author of "MySQL in a Nutshell" (O'Reilly 2005).

Edited 1 time(s). Last edit at 07/28/2005 01:30AM by Russell Dyer.

Options: ReplyQuote

Written By
July 27, 2005 11:27PM
Re: Getting to row number
July 28, 2005 01:29AM
November 25, 2008 05:54AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.