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:[EMAIL PROTECTED]) 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:[EMAIL PROTECTED]) AS row, ename, empno FROM emp ORDER BY empno
) t WHERE ename='scott';
+-----+-------+-------+
| row | ename | empno |
+-----+-------+-------+
| 8 | SCOTT | 7788 |
+-----+-------+-------+
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
