-2

I have a SQLite table sorted by column ID. But I need to sort it by another numerical field called RunTime. 

CREATE TABLE Pass_2 AS
SELECT RunTime, PosLevel, PosX, PosY, Speed, ID
FROM Pass_1

The table Pass_2 looks good, but I need to renumber the ID column from 1 .. n without resorting the records.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Nobby
  • 289
  • 2
  • 12
  • 1
    What do you mean by "reorder (NOT resort) the ID column from 1..n without resorting the records."? Either you sort the rows, or you don't sort. –  Oct 28 '14 at 13:58

2 Answers2

2

It is a principle of SQL databases that the underlying tables have no natural or guaranteed order to their records. You must specify the order in which you want to see the records when SELECTing from a table using an ORDER BY clause.

You can obtain the records you want using SELECT * FROM your_table ORDER BY RunTime, and that is the correct and reliable way to do this in any SQL database.

If you want to attempt to get the records in Pass_2 to "be" in RunTime order, you can add the ORDER BY clause to the SELECT you use to create the table but remember: you are not guaranteed to get the records back in the order in which they were added to the table.

When might you get the records back in a different order? This is most likely to happen when your query can be answered using columns in a covering index -- in that case the records are more likely to be returned in index order than any "natural" order (but again, no guarantees with an ORDER BY clause).

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Just imagine a column in a table with values of '1, 2, 5, 9, 3, 7...'. I want to overwrite the column with new values '1, 2, 3, 4, 5, 6...'. – Nobby Oct 28 '14 at 14:37
  • What is the `ORDER BY` clause that produced the list `1, 2, 4, 9, 3, 7`? – Larry Lustig Oct 28 '14 at 15:01
  • Also, see this page https://www.sqlite.org/autoinc.html for the possibility of using SQLite's hidden `rowid` column as a possible *proxy* for added-to-table order. – Larry Lustig Oct 28 '14 at 15:05
0

If you want a new ID column starting at 1, then use the ROW_NUMBER() function. Instead of ID in your query use this ROW_NUMBER() OVER(ORDER BY Runtime) AS ID.... This will replace the old ID column with a freshly calculated column

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Grantly
  • 2,546
  • 2
  • 21
  • 31
  • Sorry for lack of formatting, damn iPads....hard to use the code formats....EDIT thanks for tidying it up – Grantly Oct 28 '14 at 14:08
  • This is potentially not available in SQLlite ... Unsure whether ROW_NUMBER is supported. See http://stackoverflow.com/questions/4074257/sqlite-equivalent-of-row-number-over-partition-by – Grantly Oct 28 '14 at 14:19