-2

I have never used SQL before until today. I'm trying to look at the last row of a table (the name of the table is prices_table). The table has 3 columns, sid, prices, datetime.

I read How to select the last record of a table in SQL? and tried to use the first answer:

SELECT * FROM prices_table ORDER BY ID DESC LIMIT 1;

but it gives the error:

SELECT * FROM prices_table ORDER BY ID DESC LIMIT 1;
ERROR:  column "id" does not exist
LINE 1: SELECT * FROM prices_table ORDER BY ID DESC LIMIT 1;
                                                    ^
HINT:  Perhaps you meant to reference the column "prices_table.sid"
roulette01
  • 1,984
  • 2
  • 13
  • 26
  • 2
    What do you mean by "last"? Data is just stored, not in any particular order. If you mean by DateTime, then order by DateTime desc. If you mean by SID, then order by SID desc. – Isolated May 04 '23 at 21:05
  • If your table doesn't have an `id` column then you can't order by it, replace `id` with the column you want to order the rows by, ie `SELECT * FROM prices_table ORDER BY datetime DESC LIMIT 1;` – pilchard May 04 '23 at 21:05
  • We can't guess which column you can use as a way to determine the "last" row. It's typical to have an auto-increment column, but if you don't have that, you'll have to choose a column that you know is effectively correlated to the order you want. – Bill Karwin May 04 '23 at 21:08
  • @Isolated Oh I thought the table had an order, but I do mean the most recent `datetime`. When I print the first 10 rows of the table, it shows `datetime` in ascending order so I think the table is already sorted based on `datetime`? – roulette01 May 04 '23 at 21:09
  • @pilchard Is it standard to have an `id` column? In some posts that I read on SQL, it seems the answers assume there's an `id` column even though I did not see OP mentioning it – roulette01 May 04 '23 at 21:09
  • There's no standard, but it's common. – Barmar May 04 '23 at 21:10
  • 2
    Are you sure that you are running MySQL? This looks like a Postgres error message. – GMB May 04 '23 at 21:17
  • 2
    You can check by running this query: `SELECT VERSION();` – Bill Karwin May 04 '23 at 21:21
  • 1
    with no order by specified, you cannot count on any particular order. what order you see the rows in will depend on how mysql has decided to read them, which can change based on a number of factors. – ysth May 05 '23 at 01:00

1 Answers1

2

The order of a table is not guaranteed. It sounds like at least some of the rows are stored in order by your datetime column, but we can't assume all of them are.

To be sure, you should specify the order in your query:

SELECT * FROM prices_table ORDER BY datetime DESC LIMIT 1;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • In words, is this saying "sort the table in decreasing order based on the datetime column, and then show the first row"? – roulette01 May 04 '23 at 21:13
  • 2
    Yes, that's correct. You may want to start by reading a book or a tutoral on MySQL (or whatever brand of software you're using, if it's not MySQL). – Bill Karwin May 04 '23 at 21:21