Finding the 5 last dates... [Resolved]

[NoteMe]

I have to find exactly 5 records in a MySQL database. And it have to be the 5 with "highest" dates. They are not in the right order at all. So I have to sort them some way, and the problem is that I have to show the oldest of the 5 newest first and then the newest of the newest last.

Anyone have any idea on how to do this?




PS: I tried to take out the 5 newest one first and save the rows in diffrent variables. But since I am going in and out of PHP mode it looks like my variables are lost. So the best way would probably be if the MySQL querrery only took out the 5 newest and sorted them from the oldest to the newest.

[ober0330]

SELECT TOP 5 date_field 
FROM [Table] 
ORDER BY date_field DESC 

That gets them in the reverse order of what you want, but you can just as easily print them out backwards.

[NoteMe]

Hmmm never seen that TOP key word. What is it actualy doing, picking the 5 in the end of the table, or picking the 5 with newest dates?

[techgnome]

Originally posted by NoteMe
Hmmm never seen that TOP key word. What is it actualy doing, picking the 5 in the end of the table, or picking the 5 with newest dates?

That's because MySQL doesn't support the TOP keyword.... it uses the LIMIT instead.

This will return the last 5

SELECT date_field , .....
FROM [Table] 
ORDER BY date_field DESC
LIMIT 5

As for then showing them oldest to newest..... don't know how to do that w/o an inner select, which MySQL doesn't support yet.

What you might need to do is create a table with the same structure, use the above select to insert records into it, then select out of that table all records sorted differently. If you do this, don't forget to clear out the secondary table between uses.

TG

[NoteMe]

Thanks for the answer...I thought about using a inner querery...didn't know MySQL didn't support it......but thanks for the work around.

Your solution worked great for a while, untill we had tested it some more, and found out that the solution we need is even more complex.

Becuase there might be more then 5 new records after "todays date", didn't think about that...I must have lost my head lately.

So what we kind of need is two querreries. One that will have to test if there is more then 5 new entries after todays date, then show from todays date and the 5 next ones (remember that does not mean that the newest one have to be included. If there is 6 or more new, then they are not inluded only the 5 from today and the next ones)


But if there is not 5 new ones, then we have to go back in time and get the 5 newest ones and get them from the oldest of the 5 to the newest....


this is starting to give me a head acke...anyone want to kill the one that told me to make that site for those guys...