LIMIT and ORDER BY dropping rows!!

chops

I have a giant table called images. I am trying to grab the last six updated rows in this table. For some reason MySQL seems to be dropping the last 5 rows....here is my statement:

"SELECT * FROM images ORDER BY date LIMIT 6"

date is a timestamp of when the image was uploaded (and the row created)....so I should be getting the last 6 rows with this query....but it seems to missed the last 5 rows......

Anyone had a similar problem?....help needed

brandonschnell

shouldn't that be:

SELECT * FROM images ORDER BY date DESC LIMIT 6

chops

Unfortunatly that grabs images from the middle of my table....it makes no sense...anyways the previous query I used works fine and grabs the last images, but like I said skips the last 5

Anon

No, your query should get the earliest rows. Are you sure the timestamp is really a date/time column?

chops

My date column is just php's time(); function...I have no idea what the hell is going on...

Anon

You need to tell us what data type the date column is - if it's a text type (char, varchar, text, etc.) it could be that it's comparing the time from the 1billion to the 999 million (recent changeover) so it's not getting the correct ones - if that's the case, alter the table so that field is an INT and it should work.

chops

yes my column is a varchar and it just stores time(); from my script.

pmitride

I used the php time() function until I realised how impractical that was for queries. Use MySQL very simple date and/or time types and you\'ll have a wider range of queries. I don\'t know what the php time returned, but because it\'s a varchar type, it may not order properly.

Also, if you want to return the last 6 rows, my little SQL would count all the rows, get count -6, and query would look

limit 35,40

which will return row 35 till 40 if you have 40 rows in your query.

[deleted]

Is the size of the varchar large enough to hold the entire timestamp?

Print the value of the timestamp column to the screen and see if they really make sense.

smarlowe

Could you please post the surrounding code? Troubleshooting without code is like throwing darts in the dark. You might hit the target, you might not...