i don't know if this question should be in this forum or not. if it isn't, pls direct me to the right forum, thanks.
a table with the following attributes
data_table(year varchar2, batch varchar2, member_id varchar2, score varchar2)
some sample data
year batch memberid score
2002 1 abc 9
2002 2 abc 7
2002 3 abc 9
2002 1 xyz 8
2002 2 xyz 9
2002 1 asdf 6
i want to write a sql to retrieve the latest score (based on the batch number) of a member.
eg in the sample data, i would want to get for abc the score for 2002 batch3, for xyz the score for batch2 2002 and for asdf the score for batch1 2002.
does the following sql looks feasible?
select score from data_table DT
where year=2002 and
batch in
(
select max(batch) from data_table
where year=2002 and memberid = DT.memberid
)
thanks
a table with the following attributes
data_table(year varchar2, batch varchar2, member_id varchar2, score varchar2)
some sample data
year batch memberid score
2002 1 abc 9
2002 2 abc 7
2002 3 abc 9
2002 1 xyz 8
2002 2 xyz 9
2002 1 asdf 6
i want to write a sql to retrieve the latest score (based on the batch number) of a member.
eg in the sample data, i would want to get for abc the score for 2002 batch3, for xyz the score for batch2 2002 and for asdf the score for batch1 2002.
does the following sql looks feasible?
select score from data_table DT
where year=2002 and
batch in
(
select max(batch) from data_table
where year=2002 and memberid = DT.memberid
)
thanks