homepage Welcome to WebmasterWorld Guest from 54.167.238.60
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Rearranging Ordered Queries
itledi




msg:3589076
 2:33 am on Mar 2, 2008 (gmt 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?

 

ZydoSEO




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


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




msg:3595009
 3:19 pm on Mar 8, 2008 (gmt 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

syber




msg:3595073
 5:13 pm on Mar 8, 2008 (gmt 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

itledi




msg:3595221
 8:49 pm on Mar 8, 2008 (gmt 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 (...)

itledi




msg:3595372
 12:46 am on Mar 9, 2008 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved