Generate an integer sequence in MySQL

by

I found this solution on the web 

SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

Single query, fast, and does exactly what I wanted: now I can “number” the “selections” found from a complex query with unique numbers starting at 1 and incrementing once for each row in the result.

I think this will also work for the issue listed above: adjust the initial starting value for @row and add a limit clause to set the maximum.

BTW: I think that the “r” is not really needed.

ddsp

More Related Contents:

  1. Error related to only_full_group_by when executing a query in MySql
  2. Find duplicate records in MySQL
  3. How to declare a variable in MySQL?
  4. Can you split/explode a field in a MySQL query?
  5. MySQL foreign key constraints, cascade delete
  6. Error Code: 2013. Lost connection to MySQL server during query
  7. SQL Performance UNION vs OR
  8. Equivalent of varchar(max) in MySQL?
  9. How to escape literal percent sign when NO_BACKSLASH_ESCAPES option is enabled?
  10. MySQL MONTHNAME() from numbers
  11. Aggregate function in MySQL – list (like LISTAGG in Oracle)
  12. View’s SELECT contains a subquery in the FROM clause
  13. How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?
  14. MySQL incorrect key file for tmp table when making multiple joins
  15. How to cast DATETIME as a DATE in mysql?
  16. ORDER BY date and time BEFORE GROUP BY name in mysql
  17. Does the order of conditions in a WHERE clause affect MySQL performance?
  18. Difference between these two joining table approaches?
  19. Get total number of rows when using LIMIT? [duplicate]
  20. Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
  21. MySQL export into outfile : CSV escaping chars
  22. Simple Random Samples from a MySQL Sql database
  23. MySQL Select minimum/maximum among two (or more) given values
  24. How to Get True Size of MySQL Database?
  25. MySQL vs. JSON – Why? [closed]
  26. Find method to store an IP in MySQL for better performance
  27. MySQL add 12 hours to a time field
  28. OSX ld: library not found for -lssl
  29. Ruby on Rails – cannot load such file — mysql2/2.2/mysql2 (LoadError)
  30. MySQL: Find Missing Dates Between a Date Range

Leave a Comment