2

this is my first time asking a question, so bear with me and thanks in advance for any response I get. I am using sqlite3 on a Macbook pro. Every record in my database has a time stamp in the form YYYY-MM-DD HH:MM:SS, and I need to sort the entire database by the time stamps. The closest answer I have found to letting me do this is SELECT * FROM Table ORDER BY date(dateColumn) DESC Limit 1 from SQLite Order By Date but this returns the most recent date. I would love to be able to apply this but I am just learning sqlite can't figure how to do so.

Community
  • 1
  • 1
Jacob
  • 39
  • 1
  • 5
  • Welcome to the SO Community! Remember to upvote useful questions and answers as you go - and keep learning! – C. Tewalt Jul 22 '15 at 19:09

3 Answers3

3

Change the limit to the number of rows you want:

SELECT * FROM Table ORDER BY dateColumn DESC Limit 10000000;

you can figure out how many rows you have using

SELECT count(*) FROM Table;

and give a limit greater than that number. Beware: If you want all rows you should really put a limit, because if you don't put a limit and simply do

SELECT * FROM Table ORDER BY dateColumn DESC;

it will limit the output to a certain number depending on your system configurations so you might not get all rows.

patapouf_ai
  • 17,605
  • 13
  • 92
  • 132
  • This does what I would like, but does it sort the database or just return a result set with the data by time stamp? – Jacob Jul 22 '15 at 19:22
  • this just returns the result in the sorted order you want. There is not such thing as "sorting the database" what you can do however is index the table by date buy putting `dateColumn` as a key. This is what you could call "sorting the database". – patapouf_ai Jul 22 '15 at 19:24
  • see for example http://stackoverflow.com/questions/9843973/how-to-correctly-do-create-index-with-sqlite – patapouf_ai Jul 22 '15 at 19:27
0

When you don't want a limit, omit it. Please note that it is not necessary to call the date function:

SELECT * FROM MyTable ORDER BY dateColumn;
CL.
  • 173,858
  • 17
  • 217
  • 259
0

Just leave off the "Limit 1". The query means "SELECT *" (the star means return all the columns) "FROM Table" (kind of obvious, but from the table name you enter here) "ORDER BY date(dateColumn)" (again, somewhat obvious, but this is the sort order where you put your data column name) "DESC" (backwards sort, leave this off if you want ascending, aka forward, sort) and "Limit 1" (only return the first record in the record set).

BrianD
  • 1
  • 1