please help - strange results when using ORDER BY XX DESC

zubora

Hi everyone,
I tried searching for my particular problem but couldn't find it, so here i go.

I am trying to retrieve (3) results from my database of 21 entires in reverse order.

I tried the following sql statement and it worked just fine:
SELECT entryID, title
FROM tbljournal
ORDER BY entryID
LIMIT 18, 3

It returned:
entryID title
19 test19
20 test20
21 test21

But when I try the following statement, I get what I think is a weird result:
SELECT entryID, title
FROM tbljournal
ORDER BY entryID DESC
LIMIT 18, 3

It returns:
entryID title
3 test3
2 test2
1 test1

When I want it to return 21, 20, 19. I don't understand this - could someone please explain why this is happening and how I can get the results I am looking for?

(and I don't know if it matter, but entryID is the primary key in my table)

Thanks in advance!!

Rodders

The reason you get 3, 2, 1 is because they are the first three rows in the table listed in descending order. You haven't specified a WHERE in your SQL to tell it which rows to return.

Firstly, we'll need to know how you make the decision that you only want 19, 20 and 21 returned.

Are these the highest values in the table or is that the range you are interested in? Or is the range 15-30?

If it is a range you want, try something like this:

SELECT entryID, title
FROM tbljournal
WHERE entryID BETWEEN 19 AND 21
ORDER BY entryID desc

You shouldn't need the limit since the WHERE restricts the number of rows returned.

If you can tell us more about why you only want those 3 rows returned then we might be able to improve the SQL.

zubora

Hi Rodders,

Thanks for your reply. The 3 rows are supposed to be returned because this is for my little journal site, and the 3 rows are the 3 entries that a user can chose from to help navigate in the site. I was thinking about eventually expanding this to 5 rows, but for now I wanted to stick to 3 (because of html table issues).

The LIMIT is set at 19 because this was just an example for when the user chose entry 20 (thus the numbers are arbitrary). I wanted the list to then consist of 1 entry previous to and 1 entry prior to the selected entry (a sort of offset, I suppose). I thought that's what LIMIT was supposed to do, but I will give it a try with the WHERE statement.

One thing I am still puzzled about is, I thought LIMIT 19, 3 told mysql to start with entry 19, so I don't understand why it starts with 0. Does this mean that LIMIT does not work in conjuction with desc?

Thank you! 🙂

Rodders

I'm not too sure about LIMIT. I've never used it myself.

NOTE TO SELF: Go and learn about LIMIT !

zubora

chuckle
Fair enough - obviously I have just a lot to learn in general.

Can anyone else perhaps enlighten me?

Thank you, though- your solution seems to be at least giving me the output i want! yay 🙂