sql query

[lowbk]

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

 

[JohnMeli]

The query you wrote will not give you the MemberId - Score result...
abc 9 (batch 3)
xyz 9 (batch 2)
asdf 6 (batch 1)

but this query will probably do the job

select mamberid, score from data_table DT
where year=2002
and batch = (select max(batch) from data_table
where year=2002 and memberid = DT.memberid);

I hope this helps, if I understood well your question

John

 

[dabest]

How do we construct a SQL statement for this?
suppose i have 2 tables, TABLE A and TABLE B, and their data is shown below:

TABLE A TABLE B
---------- ________

name addr name yr salary
a canada a 2001 30,000
b us d 2002 40,000
c uk d 2003 80,000
d japan

the output must be:

name addr salary
----------------------------------------
a canada 30,000
b us
c uk
d japan 80,000



* the salary is blank when the name is not found in table B
* when there is more than one entry for a name, the salary with the latest year is used


thank you for your time

 

[SantaMufasa]

dabest, here is the SQL you requested:

select a.name, a.addr, max(b.salary)salary
from a,b
where a.name = b.name(+)
group by a.name, a.addr
/

 

[JohnMeli]

The following query will give you the max salary per name or null if there is none in the B Table

select A.NAME, A.ADDR, MAX(B.SALARY)
from A, B
where A.NAME = B.NAME (+)
GROUP BY A.NAME, A.ADDR;

The salary for the max year of name is

select A.NAME, A.ADDR, MAX(B.SALARY)
from A, B
where A.NAME = B.NAME (+)
and NVL(B.YR,0) IN (select NVL(MAX(B.YR),0)
from B
where A.NAME = B.NAME)
GROUP BY A.NAME, A.ADDR

In this query you see that I use NVL so as to change the null year to a value (0)

I hope this helps

John

 

[dabest]

Thanks guys, i'l try them..tnx! tnx!