Say I have this table. Let's say it looks something like this:
table values ( - id (int, primary key, auto increment) - object_id (foreign key on a different table. Used to search for specific object values) - year (varchar, because in someone infinite wisdom using "1981-1982" in addition to 1981 and 1982 values is a good idea) - high_price - low_price )
Now it would be easy to do if the data were just added at the end with updated prices. However, the data is not very pleasant. For example, take a look at the three-year range:
(id, object_id, year, high_price, low_price) - 259, 60, 1976, 34000, 29000 - 260, 60, 1977, 35000, 31000 - 261, 60, 1978, 36000, 35000 - 1103, 60, 1976, 29000, 25000 - 1104, 60, 1977, 36000, 32000 - 1105, 60, 1978, 38000, 33000 - 2634, 60, 1976, 34000, 30000 - 2635, 60, 1977, 37000, 33000 - 2636, 60, 1978, 40000, 35000
If I wanted to capture the 18 most recent entries for a particular year, how would I do it? It should also be in reverse order, so when I schedule it, the last year is on the right. I will not necessarily know the last year, and this may not be in the current year (most likely, it will not be in the current year).
I have an SQL statement in my php that looks like this:
$sql = "SELECT * FROM `values` WHERE `object_id`='$id' ORDER BY `year` DESC, `id` DESC LIMIT 18" $result = $mysqli->query($sql);
It seems to be getting the correct values, but putting them in the wrong order. I can just look at the list in a reverse loop, but I would like to know if there is an SQL statement that can do this for me.
Thanks!
Edit: I tried SQL with ORDER BY year ASC, id DESC and ORDER BY year DESC, id ASC . None of them work. The first one still spits them back, and the second one spits them out from the first year, not the last.