January 28, 2009 at 7:39 am
I'm trying to create a view where I pick the ID number and highest expiration date from a table. That part is no problem, however, I also want to pick the value from the field called "member_group" that is associated with the highest expiration date and that's where I run into issues since member_group isn't contained in an aggregate function or the GROUP BY clause.
How can I select the member_group field value that is associated with the maximum expiration date?
Here's what I have that isn't working.
Create view MAX_EXPIRATION as
select ID,max(expiration) as MaxExpirationDate, member_group as MemberGroup
from MEMBER
group by ID
Thank you.
Rog
January 28, 2009 at 8:16 am
Try this
CREATE VIEW MAX_EXPIRATION AS
SELECT
t1.ID,
t1.MaxExpirationDate,
m.MemberGroup
FROM member m,
(
SELECT
ID,
MAX(expiration) AS MaxExpirationDate
FROM member
GROUP BY ID
) t1 -- there should be an end paren here, but it always shows the face for me.
WHERE m.id = t1.id
AND m.expiration = t1.expiration
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 28, 2009 at 12:07 pm
Thanks! That helped reveal a new problem with the data itself! Several ID numbers have matching expiration dates but different record_group types. So now the challenge is to find the highest (max) expiration date along with the max(last_updated) date and use the record_group value for that record!
Building upon what you wrote, I'm trying to incorporate that view into a new one and not really seeing the results I need:
create view max_last_updated as
select
t1.ID,
t1.MaxLastUpdated,
m.Member_Group
from Max_Expiration m,
(Select
id,
Max(last_updated) as MaxLastUpdated
From Member
Group BY ID) t1
Where m.id = t1.id
Thanks for any help.
Roger
January 28, 2009 at 10:10 pm
The following is probably wrong, because I don't know what your data or table structure actually looks like, and I really don't think you gave us enough information to accurately help you here, but this may give you an idea of how to fix it. Row_Number makes stuff like this much easier. For future posts, or if you can't figure it out from this, please read the link in my signature on posting sample data and post accordingly.
CREATE VIEW max_last_updated
AS
SELECT
M.ID,
M.MaxLastUpdated,
M.Member_Group
FROM Member M
INNER JOIN (
SELECT M.id, MAX(M.last_updated) AS MaxLastUpdated
FROM Member M
INNER JOIN (
SELECT
M.ID,
MAX(M.expiration) AS MaxExpirationDate
FROM Member M
GROUP BY M.ID) M2
ON M.ID = M2.ID AND M.Expiration = M2.MaxExpirationDate
GROUP BY M.ID) M1 ON M.ID = M1.ID AND M.Last_Updated = M1.MaxLastUpdated
January 29, 2009 at 3:15 pm
Thank you!
Rog
January 29, 2009 at 5:53 pm
Well that's just what I was going to say:) However, a viral infection in my throat made me miss work. Thanks Seth.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply