Add a row number to result set of a SQL query

Question

I have a simple select statement  I want to add a temporary column which will number the rows in my result set  I tried this -   declare  num int set  num   0  select t A  t B  t C    count   1  as number from tableZ as t   It assigns the 1 to all rows  I tried  count    count   1 and it did not work  How do I do this thing in a simple manner    thanks

User · Answer

So before MySQL 8 0 there is no ROW NUMBER   function  Accpted answer rewritten to support older versions of MySQL   SET  row number   0  SELECT t A  t B  t C    row number   row number   1  AS number FROM dbo tableZ AS t ORDER BY t A

User · Answer

The typical pattern would be as follows  but you need to actually define how the ordering should be applied  since a table is  by definition  an unordered bag of rows    SELECT t A  t B  t C  number   ROW NUMBER   OVER  ORDER BY t A    FROM dbo tableZ AS t   ORDER BY t A    Not sure what the variables in your question are supposed to represent  they don t match

User · Answer

SELECT     t A      t B      t C      ROW NUMBER   OVER  ORDER BY  SELECT 1   AS number FROM tableZ AS t   See working example at SQLFiddle  Of course  you may want to define the row-numbering order     if so  just swap OVER  ORDER BY  SELECT 1   for  e g   OVER  ORDER BY t C   like in a normal ORDER BY clause

[sql] Add a row number to result set of a SQL query

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005