Welcome to WebmasterWorld Guest from 23.20.239.237

Forum Moderators: open

Message Too Old, No Replies

Rearranging Ordered Queries

     
2:33 am on Mar 2, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 5, 2007
posts:107
votes: 0


Hello,

I have a line of code that selects 10 articles of the most recently published articles, sorting them by publication date.

mysql_query("SELECT * FROM articles ORDER BY published DESC LIMIT 10");

How could I modify this code to still get the 10 most recently published articles, but instead of arranging them by pubplished date, I would like to arrange them alphabetically.

Is this possible in mysql?

5:28 am on Mar 2, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Nov 11, 2007
posts:769
votes: 1



If 'articleid' is the Primary Key for the articles table and you want to order the 10 most recent articles by, say, a field called 'title' then you should be able to do something like the following:

SELECT *
FROM articles
WHERE articleid IN (
SELECT articleid
FROM articles
ORDER BY published DESC)
ORDER BY title ASC

3:19 pm on Mar 8, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 5, 2007
posts:107
votes: 0


Thank you for your help, but I've still been having a devil of a time getting it to work.

Here's my current query (it works)

SELECT * FROM articles WHERE hash IN (SELECT hash FROM articles WHERE published>=DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND published<DATE_SUB(CURDATE(),INTERVAL 0 DAY) ORDER BY published DESC) ORDER BY title ASC LIMIT 5

My problem is, even thought it runs, the first running part of the query seems to accomplish little. Even though it arranges all the results, it passes all results from this to be rearranged fully to the second part.

I believe it would work great if I could limit the number of results from the first part of the query, shown by the code following, but this dosn't work.

What would seem more fit (doesn't work)

SELECT * FROM articles WHERE hash IN (SELECT hash FROM articles WHERE published>=DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND published<DATE_SUB(CURDATE(),INTERVAL 0 DAY) ORDER BY published DESC LIMIT 5) ORDER BY title ASC

5:13 pm on Mar 8, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


I assume that derived tables work in MySQL. If so, you could do this:

SELECT * FROM
(SELECT * FROM articles ORDER BY published DESC LIMIT 10) as topten
ORDER BY articles

8:49 pm on Mar 8, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 5, 2007
posts:107
votes: 0


Thank you so much! Derived tabes worked great!

SELECT * FROM (SELECT * FROM articles WHERE published>=DATE_SUB(CURDATE(),INTERVAL 1 DAY) ORDER BY published DESC LIMIT 5) AS this ORDER BY title ASC

Now I'm trying to certain another part of the page's code similiar to this. What I'm know interested in doing is using a WHERE NOT EXISTS clause, to exclude some of the results that meet certain query.

What I have is this, but need help getting it up and working:

SELECT * FROM articles WHERE published>DATE_SUB(CURDATE(),INTERVAL 24 HOUR) AND NOT EXISTS (SELECT * FROM articles WHERE published>=DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND published<DATE_SUB(CURDATE(),INTERVAL 0 DAY) ORDER BY score DESC LIMIT 5) ORDER BY published DESC LIMIT 5

It runs if I take out the clause AND NOT EXISTS (...)

12:46 am on Mar 9, 2008 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 5, 2007
posts:107
votes: 0


Upon further research, I realized this is more of a seperate issue, and that the WHERE NOT EXISTS clause isn't even the right clause.

I decided to reword my question, and submit it to a seperate post where it belongs. [webmasterworld.com...]

Thanks again for all your help.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members