MySQL Forums :: Newbie :: Getting to row number

Advanced Search

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

Subject Written By Posted
Getting to row number Matthew Reynolds 07/27/2005 11:27PM
Re: Getting to row number Matthew Reynolds 07/27/2005 11:30PM
Re: Getting (to) row number Claude Martin 07/28/2005 12:49AM
Re: Getting (to) row number Matthew Reynolds 07/28/2005 01:22AM
Re: Getting (to) row number Claude Martin 07/28/2005 01:31AM
Re: Getting to row number Russell Dyer 07/28/2005 01:29AM
Re: Getting to row number ajay sharma 11/25/2008 05:54AM
Re: Getting to row number Roland Bouman 07/28/2005 02:26PM

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.