Group Problem

[chefhung]

SELECT schedules.scheduletitle, MAX(reservations.end_date) AS end_date, MAX(reservations.start_date) AS start_date, resources.name, reservations.summary, reservations.machidFROM cwlab.schedules LEFT OUTER JOIN cwlab.resources ON schedules.scheduleid = resources.scheduleid LEFT OUTER JOIN cwlab.reservations ON resources.machid = reservations.machidWHERE schedules.scheduleid = 'cwe0000003640000' AND (reservations.end_date IS NOT NULL OR reservations.end_date IS NULL)GROUP BY resources.nameORDER BY resources.name================================================================I try to get latest end date and Null end date. And print one resources.name per group.Problem is every thing going find until to summary. The script only picks up the first summary in the group, is not match to the latest end date?What I can do to fix this problem?

[justsomeguy]

When you're using a function like MAX it's not going to match up the value with whatever row it was in. Consider this: you're asking it for both the max end_date, and the max start_date. So which summary is it going to match, is it going to give the summary for the row with that end date, or the summary for the row with that start date? What if you have 2 end dates that are the same? Which summary is it going to use?You can't return the matching data like that, you need to either get all of the records and loop through them yourself to figure out which is the max end date, or have one query that returns the max end date and then use another query to get records with that end date and print the summary out. Keep in mind there might be more than one.Also, this condition doesn't do anything:(reservations.end_date IS NOT NULL OR reservations.end_date IS NULL)Everything is either null or not null, so that always matches everything.

[chefhung]

When you're using a function like MAX it's not going to match up the value with whatever row it was in. Consider this: you're asking it for both the max end_date, and the max start_date. So which summary is it going to match, is it going to give the summary for the row with that end date, or the summary for the row with that start date? What if you have 2 end dates that are the same? Which summary is it going to use?You can't return the matching data like that, you need to either get all of the records and loop through them yourself to figure out which is the max end date, or have one query that returns the max end date and then use another query to get records with that end date and print the summary out. Keep in mind there might be more than one.Also, this condition doesn't do anything:(reservations.end_date IS NOT NULL OR reservations.end_date IS NULL)Everything is either null or not null, so that always matches everything.

There won't be same end date, like library lot of books, some books be lease out many time (person at the time per each book, may has same book, but the id won’t be same), some books never lease out (won’t has end date, which is null).I want to list out the latest date for all the books and the books with NULL date.But the summary can’t match to the latest date, it is only pick up first one.Please help me how fix this problem.

[justsomeguy]

But the summary can’t match to the latest date, it is only pick up first one.

Yes, I understand that, it's because you're using MAX. You can't return the max date and then all of the fields in whatever record had the max date. Consider if your data had these rows:

end_date		  summary-------------------------9/17/2009		 one9/17/2009		 two9/17/2009		 three

You're asking it for the max end date, which is 9/17/2009. Which summary will it return? There are three rows that all have that date, so how does it know which one you want?Read what I wrote last time, that's what you need to do.