[SOLVED] GROUP

[jaymc]

When using GROUP how can you get it to return the highest value in one of the affected rows

 

for example, if there are 20 usernames in a table, for each row there is a time field of which are all different

 

if we are doing GROUP BY username, how can I get it to pull out the highest time too

 

At the moment I am unable to have any control of what time field it pulls out. I have tried using ORDER by and MAX() but it doesnt work

[rhodesa]

MAX Should work:

 

SELECT username,MAX(time) FROM tablename GROUP BY username

[mwasif]

Look at http://jan.kneschke.de/projects/mysql/groupwise-max

[jaymc]

MAX Should work:

 

SELECT username,MAX(time) FROM tablename GROUP BY username

 

That worked, but not when I put the rest in

 

$querya = "SELECT count(views) as count, SUM(views) as viewers, gallery, timestamp, MAX(valid)

FROM gallery

WHERE gallery.user = 'peter'

GROUP BY gallery";

[rhodesa]

What is the error message you are getting? 'timestamp' will give you problems, because you are grouping on gallery. Put a MAX() or something around that too.

[jaymc]

There is no error message, it just displays what ever it wants

 

I cant put a MAX around timestamp as that will conflict with the max around valid

[fenway]

At the moment I am unable to have any control of what time field it pulls out. I have tried using ORDER by and MAX() but it doesnt work

Can you give us an example?

[jaymc]

Sure

 

$q = "SELECT count(views) as count, SUM(views) as viewers, gallery, MAX(valid), timestamp

FROM gallery

WHERE user = 'jamie'

GROUP BY gallery";

 

Or if you mean an example of data returned, well, its not returning the row in that GROUP which has the highest value for valid.

 

Its as if it ignores that altogether

[fenway]

I'm not sure what you mean... "the row"? It will get many rows... and timestamp is meaningless with aggregate functions.

[jaymc]

Ok, its for a gallery system

 

You click on a member and it will display there galleries. They may have 1 gallery they may have 20, it displays each of there gallerys. Of course if you click on one of the gallerys it then goes in and displays all the pictures but thats irrelivent

 

The point is and my problem, I want a certain picture displayed for each of the galleries, the image is the one which has the highest value for valid

 

if there where 10 images and I did a GROUP, it would select one of them images by default

 

I want to choose which image is selects as default

 

As well as doing that, as part of image name from the row returned, I also get the gallery name which is the object

[fenway]

Ah.. .this sounds like the standard groupwise maximum issue... you can't do both without a join.

[jaymc]

Dam, ok thanks