Mysql: interesting ORDER/LIMIT question...

Hello!

I've got a table called "names" like this:

name, lastchangedate
peter, 2005-02-07
susa, 2003-01-23
mike, 2006-10-24
george, 2004-09-21
angelina, 2001-02-03

Now I want the latest 3 rows in ascending order. How do I do that?

this query delivers the right 3 entries, but in the wrong order:
"SELECT * FROM names order by lastchangedate desc limit 3;"

this one is not an option because I don't know how many rows there are:
"SELECT * FROM names order by lastchangedate limit 2,3;"

Thank you for any suggestions!

Have a nice day,

Klemens / Vienna(Austria)

 

You can first count the number of rows then use
"SELECT * FROM names order by lastchangedate limit 2,3;"

To count number of rows:

dev.mysql.com/doc/refman/5.0/en/counting-rows.html

 

Thank you infonote, but I'd like to have a solution in ONE query!

 

By latest, do you mean the latest according to the field lastchangedate? and by ascending, do you mean ascending in date or name order?

 

Yes, latest by date (lastchangedate), and ascending order of the date

 

"SELECT * FROM names ORDER BY lastchangedate ASC LIMIT 3;"

 

that returns the three rows with the oldest date, not the newest three!

 

IC....lets try this

select * from(select * from names where lastchangedate < now() limit 3) as new order by lastchangedate asc;

 

create temporary table temp_names SELECT * FROM names ORDER BY lastchangedate DESC LIMIT 3;
select * from temp_names order by lastchangedate;

Code (markup):

 

OK, thank you for the idea with the subselect. I'm still used to not having subselects from MySQL 3.x...

That's what I finally used:
select * from (select * from names order by lastchangedate desc limit 3) as new order by lastchangedate;

 

Ok...Klemens good luck