List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 18 2005 4:29am
Subject:Re: Ordinal number within a table
View as plain text  
In the last episode (Jun 17), Ed Reed said:
> Is there way to return the ordinal position of a value within a table?
>  
> Let's say I have a table of phone numbers. Over time the table has
> had additions and deletions. The table has an autonumber ID field. If
> I sort by the ID field I'd like to know what position the number
> '555-1212' is in the table.

In Oracle you could simply use the internal "rownum" column.  In MySQL,
you can use a user variable in a subquery to keep a row count during
the select process:

SET @row=0;
SELECT (@row:=@row+1) AS row, ename, empno FROM emp ORDER BY empno;
+-----+--------+-------+
| row | ename  | empno |
+-----+--------+-------+
|   1 | SMITH  |  7369 |
|   2 | ALLEN  |  7499 |
|   3 | WARD   |  7521 |
|   4 | JONES  |  7566 |
|   5 | MARTIN |  7654 |
|   6 | BLAKE  |  7698 |
|   7 | CLARK  |  7782 |
|   8 | SCOTT  |  7788 |
|   9 | KING   |  7839 |
|  10 | TURNER |  7844 |
|  11 | ADAMS  |  7876 |
|  12 | JAMES  |  7900 |
|  13 | FORD   |  7902 |
|  14 | MILLER |  7934 |
+-----+--------+-------+
SET @row=0;
SELECT * FROM (
 SELECT (@row:=@row+1) AS row, ename, empno FROM emp ORDER BY empno
 ) t WHERE ename='scott';
+-----+-------+-------+
| row | ename | empno |
+-----+-------+-------+
|   8 | SCOTT |  7788 |
+-----+-------+-------+

-- 
	Dan Nelson
	dnelson@stripped
Thread
Ordinal number within a tableEd Reed18 Jun
  • Re: Ordinal number within a tableDan Nelson18 Jun
  • Re: Ordinal number within a tableJigal van Hemert18 Jun
    • Re: Ordinal number within a tablemfatene18 Jun
Re: Ordinal number within a tableNishikant Kapoor28 Jun
  • Re: Ordinal number within a tablemfatene28 Jun
RE: Ordinal number within a tableNishikant Kapoor28 Jun
  • RE: Ordinal number within a tablemfatene28 Jun
    • Re: Ordinal number within a tableMichael Stassen29 Jun
      • Re: Ordinal number within a tableMathias29 Jun