Solved

How to GET Newest 30 records then SORT THEM ??

Posted on 2004-10-01
5
339 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:RickDai
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 50 total points
ID: 12204623
If you're using version 4 or better, try this:

SELECT * FROM
(SELECT * FROM prod_detail ORDER BY prod_new DESC LIMIT 30) as s1
ORDER BY prod_name
0
 
LVL 2

Author Comment

by:RickDai
ID: 12204640
That didnt work it gave me an error:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM prod_detail WHERE prod_active = 1 ORDER BY
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12204668
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.
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 12204864
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.
0
 
LVL 2

Author Comment

by:RickDai
ID: 12205999
4.1 worked. thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql sql statement - SQL INSERT INTO SELECT 11 64
mySql Syntax 7 53
Amazon Redshift 2 33
Using coldfusion <cfexecute> to restore mysql database 7 27
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now