MySQL - order by in a different order?

-=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- (c) WidthPadding Industries 1987 0\|435\|0 -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=- -=+=-
Socoder -> Web Development -> MySQL - order by in a different order?
Thu, 15 Sep 2011, 20:19
HoboBen	I almost have this query working, however it gives the timestamps in the wrong order. However, as I understand it group by has nothing to do with sorting (grouping always happens before ordering). In this image, I am trying to get a list of all articles, with the only the latest revision entry returned for each article. When two revision share an articleId, they are revisions of the same article. I am also joining the user table to get the user.name based on the revision.userId/user.id keys (this part is working). So right now, I am successfully returning only one revision for each articleId. But I am, however, only returning the oldest revision. How would I amend my query to do the same for the newest revision for each article? Thanks -- I hope that made sense! --- Reading around, it seems I will probably have to embed a query inside this query somehow. -=-=- blog \| work \| code \| more code
Thu, 15 Sep 2011, 20:44
HoboBen	I think I have this fixed: select revision1.*, user.id, user.name from revision revision1, user where user.id = revision1.userId and revision1.revisionTime = (select max(revision2.revisionTime) from revision revision2 where revision2.articleId = revision1.articleId); +----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+ \| id \| articleId \| userId \| sectionId \| visible \| revisionTime \| publishedDate \| title \| synopsis \| body \| id \| name \| +----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+ \| 3 \| 1 \| 3 \| 1 \| 1 \| 2011-09-16 02:45:01 \| 2001-02-03 \| Test Title \| Test Synopsis \| Test Body \| 3 \| test2 \| \| 4 \| 2 \| 1 \| 1 \| 1 \| 2011-09-16 02:45:05 \| 2001-02-03 \| Test Title \| Test Synopsis \| Test Body \| 1 \| dev0 \| +----+-----------+--------+-----------+---------+---------------------+---------------+------------+---------------+-----------+----+-------+ 2 rows in set (0.00 sec) --v A bit ugly, considering how easy the first was. However I'm happier not using "group by" as it's a dodgy command. -=-=- blog \| work \| code \| more code
Fri, 16 Sep 2011, 12:32
HoboBen	Dunno if anyone will know the answer to this, but is that embedded subquery going to run for every row in the table? -=-=- blog \| work \| code \| more code
Fri, 16 Sep 2011, 13:08
JL235	I'd be pretty sure it won't run on every row.
Fri, 16 Sep 2011, 13:51
HoboBen	Benchmarking, for 10,000 revisions of 50 articles: select revision1., user.id, user.name from revision revision1, user where user.id = revision1.userId and revision1.revisionTime = (select max(revision2.revisionTime) from revision revision2 where revision2.articleId = revision1.articleId) order by revision1.publishedDate desc 51 rows in set (8.04 sec)* // can reduce to 6.75 sec by comparing revision.id instead of revision.revisionTime select revision., user.id, user.name from revision, user where revision.userId = user.id group by revision.articleId; 50 rows in set (0.11 sec)* 8.04 seconds vs 0.11 seconds... a bit worrying. Also, I have no idea why but I have two rows for article.id=36 in the first query! Additionally the second (faster) query doesn't actually pick the correct userId. Bloody databases. ----- Good news though, it appears the query is cached -- it takes 0.0 seconds if you call it twice in a row! That helps slightly, but it won't always work. Any suggestions on how I might rewrite the query? Perhaps it's time to denormalise and have a second table. -=-=- blog \| work \| code \| more code
Fri, 16 Sep 2011, 14:48
HoboBen	Sod it. mysql> describe article; +------------+------------------+------+-----+---------+----------------+ \| Field \| Type \| Null \| Key \| Default \| Extra \| +------------+------------------+------+-----+---------+----------------+ \| id \| int(10) unsigned \| NO \| PRI \| NULL \| auto_increment \| \| revisionId \| int(10) unsigned \| NO \| UNI \| NULL \| \| +------------+------------------+------+-----+---------+----------------+ --v select revision.* from article, revision where article.revisionId = revision.id; 50 rows in set (0.00 sec) Slightly more work for PHP to do (and hence more places where the database will have to be locked by PHP to ensure correctness), but it's quicker and simpler than the alternative! -=-=- blog \| work \| code \| more code