Home > Archive > PHP SQL > September 2004 > msql and group by problem when using MAX() as part of select
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
msql and group by problem when using MAX() as part of select
|
|
| Jason Engel 2004-09-08, 9:00 am |
| TABLE2 --------------
id : link : title
1) 1 : alpha : title1
2) 2 : alpha : title2
3) 3 : alpha : title3
4) 4 : alpha : title4
-----------------------
select max(id), title from table2 group by link
returns:
max(id) : title
1) 4 : title1
WHY DOES IT NOT RETURN (pulls hair out!)
max(id) : title
1) 4 : title4
| |
|
|
Because you didn't ask for a specific row, you asked for a summary of
all rows and some other stuff unrelated to it. The title returned is
(probably) the first one in the table, but is not guaranteed to be from
any particular row.
For MySQL 4.x+ only use a subquery:
SELECT title, link, id
FROM table2
WHERE id IN (SELECT MAX(id) FROM table2 GROUP BY link)
For earlier MySQL, use two queries:
SELECT MAX(id) AS MAXID
FROM table2
GROUP BY link
Then construct the main query using the result:
$lngMaxID = $t_datRow[ 'MAXID' ];
SELECT title, link, id FROM table2 WHERE id = $lngMaxID
---
Steve
| |
| Jason Engel 2004-09-08, 9:00 am |
| THANKS!
The First SQL statement looks like it'll be perfect. I've tried it on my
MySQL server (4.0.20a-nt) and it doesn't work?!
Any reason why it wouldn't work? Or are nested selects not yet supported in
production version. (I've got latest nt production version)
Thanks
Jason
"Steve" <googlespam@nastysoft.com> wrote in message
news:chms22$qb9@odak26.prod.google.com...
>
> Because you didn't ask for a specific row, you asked for a summary of
> all rows and some other stuff unrelated to it. The title returned is
> (probably) the first one in the table, but is not guaranteed to be from
> any particular row.
>
>
> For MySQL 4.x+ only use a subquery:
>
> SELECT title, link, id
> FROM table2
> WHERE id IN (SELECT MAX(id) FROM table2 GROUP BY link)
>
>
> For earlier MySQL, use two queries:
>
> SELECT MAX(id) AS MAXID
> FROM table2
> GROUP BY link
>
> Then construct the main query using the result:
>
> $lngMaxID = $t_datRow[ 'MAXID' ];
> SELECT title, link, id FROM table2 WHERE id = $lngMaxID
>
> ---
> Steve
>
| |
|
|
| Jason Engel 2004-09-08, 3:57 pm |
| I've taken the plunge and upgraded to 4.1. Are gamma releases stable?
Thanks a lot for your help
"Steve" <googlespam@nastysoft.com> wrote in message
news:chn06c$t55@odah37.prod.google.com...
>
> Sincere apologies: I rechecked the reference, and it should have been
> MySQL 4.1+.
> See http://dev.mysql.com/doc/mysql/en/Subqueries.html
>
> ---
> Steve
>
| |
| Andy Hassall 2004-09-08, 8:56 pm |
| On Wed, 8 Sep 2004 14:26:14 +0100, "Jason Engel" <j.engel@defacto.com> wrote:
>I've taken the plunge and upgraded to 4.1. Are gamma releases stable?
Not officially, else it'd be the "Generally Available" version.
Presumably it's deemed more stable than a beta, which in turn's more stable
than an alpha, but it's not a full release yet.
--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
| |
| G Roydor 2004-09-09, 3:58 pm |
|
Jason Engel a écrit:
> TABLE2 --------------
> id : link : title
> 1) 1 : alpha : title1
> 2) 2 : alpha : title2
> 3) 3 : alpha : title3
> 4) 4 : alpha : title4
> -----------------------
>
> select max(id), title from table2 group by link
normalement vous devriez écrire
normally you should write
select max(id), title from table2 group by title
essayez de faire deux requetes :
try to make two requetes
1) la première sera stockée sous le nom grouptable2 (as VIEW)
the first will be stored under the name grouptable2 (as VIEW)
SELECT max([TABLE2].[id]) AS id, link FROM TABLE2 GROUP BY link
2) la deuxième appellera TABLE2 et grouptable2
SELECT test_max.id, test_max.title
FROM TABLE2 INNER JOIN grouptable2 ON (TABLE2.link = grouptable2.link)
AND (TABLE2.id = grouptable2.id);
>
> returns:
>
> max(id) : title
> 1) 4 : title1
>
> WHY DOES IT NOT RETURN (pulls hair out!)
>
> max(id) : title
> 1) 4 : title4
>
>
| |
| G Roydor 2004-09-09, 3:58 pm |
|
G Roydor a écrit:
>
>
> Jason Engel a écrit:
>
>
>
> normalement vous devriez écrire
>
> normally you should write
> select max(id), title from table2 group by title
>
> essayez de faire deux requetes :
> try to make two requetes
> 1) la première sera stockée sous le nom grouptable2 (as VIEW)
> the first will be stored under the name grouptable2 (as VIEW)
>
> SELECT max(TABLE2.id) AS id, link FROM TABLE2 GROUP BY link
>
> 2) la deuxième appellera TABLE2 et grouptable2
>
> SELECT TABLE2.id, TABLE2.title
> FROM TABLE2 INNER JOIN grouptable2 ON (TABLE2.link = grouptable2.link)
> AND (TABLE2.id = grouptable2.id);
après correction
Sorry
GR
>
>
>
>
|
|
|
|
|