Welcome to WebmasterWorld Guest from 100.25.214.89

Forum Moderators: open

Message Too Old, No Replies

Group by Issue

group by

     
10:35 am on Jul 30, 2007 (gmt 0)

New User

10+ Year Member

joined:June 18, 2006
posts:19
votes: 0


Okay I'm sure this has been asked before, I've read a few threads about this already. But it doesn't seem to help. Here is what going on.

I have a table, called musicvideos. In this table I have a number of data about the videos, such as title, creator, date, song, image, download link, stream, studio, etc.

What I like to do is make a table, of all the creators (there is a total of 10) and make a table showing only the most up to date rows.

The problem is on a simpe qurey like SELECT * FORM musicvideos WHERE studio="first" ORDER BY date DESC gives me every entry back, this results in all the entries but they're order by the latest date.

But I want it only to show 10 and only one per creator. So I have to use GROUP BY to group the creator cols up.

So I have SELECT * FROM musicvideos WHERE studio="first" GROUP BY creator ORDER BY date DESC.

Bing go, almost what I want I get the limit of 10 creators and they are sorted by the dates, however it gives me the first entry of the database for each. Now I've try a number of stuff to get GROUP BY to give me the last entry of date, I even try to add date to GROUP BY but then it gave me one entery for every date there was, which wont work. In this gurey I'll also have a COUNT('videonames') as vcount so that I can show a row in the table of how many videos they have in the database.

The problem is I wish to not write a qurey for each editor as more could be added or removed. I know it can be done in one qurey, but how is the problem. I know that GROUP BY will only give you the first entry of whatever your grouping by, in this case creator and then orders those by whatever you set order by. Because of this no matter what I do to order by it'll never give me the latest date entry. Another issue is I can't get the ID rows, because the dates and ID rows are not in order. Because id 6 may have a date from 2007 and id 55 may have a date for 2004. So ordering it that way would put the dates out of order.

I've try MAX(date) in the select but seem to have no effect. Maybe I"m doing it wrong.

SELECT *, MAX('date') as mid FROM musicvideos WHERE studio='first' GROUP BY creator ORDER BY mid DESC

This gives me the same results, that is unless I'm using MAX wrong. I'm not new to mysql but I am new to all the advance stuff. Like I've mostly only used it to grab one row or id or maybe show a list of stuff, but never to where I only show a set number of data and then sort it by total entries.

I thought that I may have to split the tables into two and do a LEFT JOIN, which I've never used before but will be soon when I get to my comment system. Any help or pointers would be a great help. If my example are confusing I'm sorry and will try to give better ones.

4:51 am on Aug 4, 2007 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


Try this:

SELECT DISTINCT creator, title, song FROM musicvideos WHERE studio="first" ORDER BY date DESC

Also if you want to specifically limit the # of records returned, you can add LIMIT:

SELECT DISTINCT creator, title, song FROM musicvideos WHERE studio="first" ORDER BY date DESC LIMIT 10

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members