1

How can I get SELECT results ordered by row id if table does not have auto-increment column/key?

Additionally, how can I for example get last 100 of them, using LIMIT, if I cannot use ORDER BY clause, as no auto-increment row is present?

Ωmega
  • 42,614
  • 34
  • 134
  • 203
  • http://stackoverflow.com/questions/12707646/need-a-sequence-number-for-every-row-in-mysql-query – Mihai Sep 16 '13 at 17:15
  • 1
    Does the table have an `ID` column ? if so - do you update it manually upon every insert ? if yes - use it, if not - how can you `order by` something which doesn't exist? I got a feeling that your question is not clear to me... – Nir Alfasi Sep 16 '13 at 17:15
  • Can you provide the current table structure, and/or point out how is row-id being computed? – hjpotter92 Sep 16 '13 at 17:18
  • There is no ID column in the table. – Ωmega Sep 16 '13 at 17:19
  • possible duplicate of [select increment counter in mysql](http://stackoverflow.com/questions/13566695/select-increment-counter-in-mysql) – jmilloy Sep 16 '13 at 17:33

5 Answers5

1

You can try the following approach, although I'm not giving any guarantee AT ALL that it'll work like you want to:

SET @rownum:=0;

SELECT *
FROM (SELECT @rownum:=@rownum+1 as rownum,
             *
      FROM yourTable) t1
ORDER BY rownum DESC
LIMIT 100;

I do have to mention that if you don't have a unique ID in your table, sorting will always be a sore issue. You'll never be able to tell the order in which things are displayed. (In other words: it's not reliable!)

Marty McVry
  • 2,838
  • 1
  • 17
  • 23
1

In a relational database there is no sorted order or a first or last row. If you want to have a particular order, you have to specify a column in an ORDER BY clause. You might get the same result for a simple SELECT whatever FROM yourTable 500 times in a row, but that behaviour can change quickly when an index is rebuild or simply more and more rows are added.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
1

select * from <table_name> order by _rowId;

Pratik Khadloya
  • 12,509
  • 11
  • 81
  • 106
0

It's hard to answer your question without seeing table schema and example queries you want to ask. In particular, it is hard to imagine what the "last 500" means if there is no sort order specified. Since it is not guaranteed to be consistent anyways, why would you care for the "last" 100 instead of the "first" 100?

1) You can get add row numbers to a query using an incrementing variable, such as Marty McVry suggested (or select increment counter in mysql or Need a sequence number for every row in MySQL query etc).

2) If you want to keep the current natural order, I would suggest added an auto-incrementing 'id' column to the table, update the rows using an incrementing variable like described above, and set the AUTOINCREMENT. Then you will be able to order by this column consistently in the future.

3) Finally, as a gimmick, I tried to get the "last 100" without using an incrementing variable, but it isn't really possible without a stored procedure. Neither of these types of queries work:

SELECT * FROM t LIMIT count(*)-100,100;

SELECT @n := count(*) FROM t;
SELECT * FROM t LIMIT @n-100, 100;
Community
  • 1
  • 1
jmilloy
  • 7,875
  • 11
  • 53
  • 86
0

This one (see below) works, but it is kinda slow...

SET @last = 100;
SET @ofst = (SELECT COUNT(*) FROM table) - @last;
SET @stmt = CONCAT('SELECT * FROM table LIMIT ', @ofst, ', ', @last);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Ωmega
  • 42,614
  • 34
  • 134
  • 203