Need some condition for GROUP BY

[orlio]

Hey, I need some help with the following SQL-query:

SELECT DISTINCT
SharingRule.Created,
Organization.Id
FROM
SharingRule
LEFT JOIN
Organization ON Organization.OrganizationId = SharingRule.OrganizationId
WHERE
SharingRule.ChannelId = 1
GROUP BY
SharingRule.OrganizationId
ORDER BY
SharingRule.Created DESC

This returns SharingRule.Created and Organization.Id which is grouped by Organization.Id. So far so good. BUT there can be many SharingRule:s for an Organization and I just want to pick the row with the latest one (the one with highest value in SharingRule.Created). In other words, I want each group to contain the SharingRule with the highest SharingRule.Created. How do I put such a condition on the GROUP BY?

[triassic]

Use MAX.

SELECT DISTINCT
MAX(SharingRule.Created),
...

[orlio]

Um.. that doesn't work. It picks the biggest SharingRule.Created value, but the other values is not the ones that correspond to that Created-value.

Use MAX.

SELECT DISTINCT
MAX(SharingRule.Created),
...

[Phill Pafford]

Hmm I would have suggested what triassic has posted. You might try the HAVING clause with the MAX(), something like:


SELECT DISTINCT
SharingRule.Created,
Organization.Id
FROM
SharingRule
LEFT JOIN
Organization ON Organization.OrganizationId = SharingRule.OrganizationId
WHERE
SharingRule.ChannelId = 1
GROUP BY
SharingRule.OrganizationId HAVING MAX(SharingRule.Created)
ORDER BY
SharingRule.Created DESC


Syntax might not be correct so look at: http://www.w3schools.com/SQL/sql_having.asp