[thelist] Incorrect SQL?

[Lachlan Cannon]

Joshua Olson said:

> Let's take a look at a possible new version of the query:
>
> SELECT
>    entryTitle
>  , DATE_FORMAT(entryDate,'%d-%m-$Y') as entryDate
>  , DATE_FORMAT(entryDate,'%W, %M %D') as entryLongDate
>  , DATE_FORMAT(entryDate,'%T') as entryTime,
>  , entryEntry
>  , count(blogComments.*) as entryComments
> FROM blogEntries, blogComments
> WHERE blogEntries.entryId = blogComments.entryId
> GROUP BY blogEntries.entryId
> ORDER BY 2 DESC
> LIMIT 7
>
> If EntryId is NOT the PK but you know it's unique anyway, you could
> technically wrap all the of NON-GROUPED fields in the SELECT statement
> with Min() or Max(), just to make the parser happy.
>
> Also, if entryId is NOT the PK, is there any reason it shouldn't be?

It is the PK. That's kinda the only reason to have it there, isn't it?
>
> Oh yeah, some databases do not like ORDER BY on a calculated field.
> Better to use the column number, usually.  In this case, I use ORDER BY
> 2, which indicates to use the second column--the entryDate column

Does that mean the second column as listed in the table? Because then it's
the third column. It seems a bit too inflexible attaching it to the number
of the column though, couldn't it become ORDER BY blogEntries.entryDate ?

Thanks a heap

Lach

BTW, are there any good downloadable resource for SQL? Something like the
PHP manual, I'm imagining. Am I just too hopeful there?