Mysql – Is it possible to reliably number rows in some order conforming to SQL standard

MySQLselectsql-standardwindow functions

For example, if I have a table with single column numbers and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith biggest/smallest).

If the DB doesn't have row_number function (MySQL, for example), how would one do that?

If I understood correctly, the order in which SELECT return the rows in unspecified and could be inconsistent.

And something like this (proposed in many answers to the similar question)

SET @num = 0;
SELECT number, @num := @num + 1 as num FROM ... ORDER BY number

Is wrong, since it seems that there is no guarantee regarding the time when ORDER BY would be evaluated. I.e. it can sort the numbers first, and then number them using @num, or it could do the opposite and sort after all the rows were numbered. Am I correct?

And using inner selects won't help, since it's not guaranteed that the order will be preserved.

Best Answer

There is a way to do that using only SQL 89 feature, but it will be horribly slow on any real world sized table:

select t1.id, 
       ... other columns ...,
       (select count(*) 
        from the_table t2 
        where t2.id <= t1.id) as row_num
from the_table t1
order by t1.id;

This assumes id is the column to sort the numbers by.

Related Solutions

Mysql – Integer comparison in MySQL. does it cause lots of overhead

My answer assumes that you are describing a simple query like this:

SELECT `Number` FROM `Table` WHERE `Number` <= 12040670 ORDER BY `Number` DESC

From your question, it's not clear how you're storing the numbers. If the column isn't an integer data type, you should make one (probably INT UNSIGNED). That will help the performance whether the column is indexed or not.

There probably should be an index on the numbers column; it will significantly increase the speed of the query. If the numbers column is the PRIMARY key, then it's already indexed.

The overhead of integer comparison isn't really relevant here; if the numbers are INTs, the comparison is very simple and efficient and if they aren't, the data types of the data and test value would be cast to the same type, which doesn't cost much at all. The real concern would be having to read the entire table if there isn't an index available.

1,000,000 rows is nothing to MySQL. If this query uses an index, there will be practically no load from it. If there is no index and it has to read the entire table, there may be some load and the server may need to hit the disk if the table isn't all in memory, but this query is simple and it probably wouldn't cause significant load. It may take up to a second to complete, but unless you would be running the query with a different reference number frequently, you probably wouldn't notice the query.

Way, in a single SQL statement to ensure that all items in a list are present in some column

If I understand what you're trying to achieve, then I think what you want is a CTE with a UNION list of all the possible tags you want to compare against, then RIGHT JOIN the tag table against the CTE.

Example -

WITH ListOfTags (Tag) AS (SELECT 'Foo' UNION SELECT 'bar' UNION SELECT 'other' UNION SELECT 'thing')
SELECT  *
FROM    item_tag    i
INNER JOIN  tags    t
    ON i.tag_id = t.tag_id
RIGHT JOIN ListOfTags   l
    ON  l.Tag like t.tag
WHERE   i.item_id is null

This might not be the most elegant way of doing it, but it might work for you

Best Answer

Related Solutions

Mysql – Integer comparison in MySQL. does it cause lots of overhead

Way, in a single SQL statement to ensure that all items in a list are present in some column

Related Question