Welcome to WebmasterWorld Guest from 54.226.25.231

Forum Moderators: open

Message Too Old, No Replies

Rearranging Ordered Queries

     

itledi

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

5+ Year Member



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?

ZydoSEO

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

WebmasterWorld Senior Member 5+ Year Member




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

itledi

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

5+ Year Member



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

syber

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

10+ Year Member



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

itledi

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

5+ Year Member



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 (...)

itledi

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

5+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month