GROUP BY question

slick101

hey everyone, i need some help.

here is my query

SELECT uid,options,date FROM feed WHERE type = 'images' GROUP BY uid ORDER BY date DESC LIMIT 6

I want to group uid all together but If the date is not the same do not group them i would like a new group row? Is this possible?

bradgrafelman

Try doing a "GROUP BY date, uid" ?

EDIT: Actually, that probably won't work either - you might need a much more complex query (e.g. a subquery) to solve this. I'm afraid I'll have to step aside and let a DB guru confirm/make a suggestion.

slick101

bradgrafelman;10916183 wrote:
Try doing a "GROUP BY date, uid" ?

EDIT: Actually, that probably won't work either - you might need a much more complex query (e.g. a subquery) to solve this. I'm afraid I'll have to step aside and let a DB guru confirm/make a suggestion.

I tried date, uid before but it was not the results I am looking for 😕

CoderDan

grouping by id then date - GROUP BY id, date - should work, if put in that order. If it doesn't, then if you provide a subset of your data, and example of what results you'd like to see, I'll figure out how.

slick101

CoderDan;10916263 wrote:
grouping by id then date - GROUP BY id, date - should work, if put in that order. If it doesn't, then if you provide a subset of your data, and example of what results you'd like to see, I'll figure out how.

actually yes uid,date does work I guess my problem is date is DATETIME field and the times are different but for 4 they were teh same so those were grouped except for the other uid. Basically they were a 1-2 seconds apart i would like to have those grouped together but if its at least maybe 5hrs i guess apart to create a new group i guess i need to setup a wheres clause with a date function?

CoderDan

you just need to pull as much of the datetime as you want for granularity.
ie, DATE( date ) would pull just the date component with no time, and as a group filter would group them all on the same day.
or you could pull FLOOR( UNIXTIME( date ) / 21600 ) to get 6 hour intervals.
fiddle around till you get what you want 🙂

slick101

CoderDan;10916286 wrote:
you just need to pull as much of the datetime as you want for granularity.
ie, DATE( date ) would pull just the date component with no time, and as a group filter would group them all on the same day.
or you could pull FLOOR( UNIXTIME( date ) / 21600 ) to get 6 hour intervals.
fiddle around till you get what you want 🙂

thanks man, i will mess around with it!