RickDai
asked on
How to GET Newest 30 records then SORT THEM ??
I am using mySQL as my DB provider.
I want to get the newest products in the table, the field is named: prod_new (DATE type)
my SQL:
"SELECT * FROM prod_detail ORDER BY prod_new DESC LIMIT 30"
This would get the latest items, because it is getting the date in DESC order.
BUt how would I be able to sort these records by the name?
"SELECT * FROM prod_detail ORDER BY prod_new DESC, prod_name ASC LIMIT 30"
I tried above, but as i suspected it orders the date then order the name.
What I want is that it gets the 30 records, and from the 30 records it will reorder them by their name regardless of their date.
I want to get the newest products in the table, the field is named: prod_new (DATE type)
my SQL:
"SELECT * FROM prod_detail ORDER BY prod_new DESC LIMIT 30"
This would get the latest items, because it is getting the date in DESC order.
BUt how would I be able to sort these records by the name?
"SELECT * FROM prod_detail ORDER BY prod_new DESC, prod_name ASC LIMIT 30"
I tried above, but as i suspected it orders the date then order the name.
What I want is that it gets the 30 records, and from the 30 records it will reorder them by their name regardless of their date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My bad. You've got to have 4.1 or better for the code above to work. I think the only way to do it without upgrading your MySQL is to re-sort it with the programming language you use to fetch the data.
I haven't encountered a good single-query way to get the top (or bottom) N from a group without using subqueries. You can use a temporary table - use your existing query to select the records into a new table, then select from that table in the order you want.
ASKER
4.1 worked. thanks.
ASKER
[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]