Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Easy SQL question
wbailey_at_pomb.com wrote in message <73sj8i$skv$1_at_nnrp1.dejanews.com>...
>Forgive my lack of SQL knowledge, but I can't figure this query out....
>
>I have a table x with the following fields: Name, Age, Weight and Date.
Each
>time a person is weighed, the record is not replaced, but just versioned by
>adding a new record with the same name, current age and new weight & date.
>What I need to do is, given a person's name, query the table to get the
most
>recent weighing for each age. eg:
>Name Age Weight Date
>---------------------------
>Joe 12 110 10/10/90
>Joe 13 130 12/23/91
>...
>
>I can figure out how to retun the most recent weighing given the name, but
I'm
>stuck on doing it for each age... any ideas?
select t1.name, t1.age, t1.dateweight, t2.weight
from
(select name, age, MAX(dateweight) as dateweight from testtab group by name, age) t1,
(select * from testtab) t2
where t1.name=t2.name and t1.age=t2.age and t1.dateweight=t2.dateweight and
t1.name='Joe' and t1.age=14;
2 Beer is not 2 Bear !
begin 666 Vadim Petrovich Shatalov.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..E-H871A;&]V.U9A9&EM.U!E
M=')O=FEC: T*1DXZ5F%D:6T_at_4&5T<F]V:6-H(%-H871A;&]V#0I414P[5T]2
M2SM63TE#13HW+3_at_T-CDM,C0U-#$W#0I!1%([2$]-13H[.SL[.SM2=7-S:6$-
M"DQ!0D5,.TA/344Z4G5S<VEA#0I%34%)3#M04D5&.TE.5$523D54.G-K:79
M861M:V]M+G1L="YR=0T*14U!24P[24Y415).150Z=&]P+6UO9&5L0&%T=&%C
G:RYR=0T*4D56.C$Y.3_at_Q,C Q5# U,C8T,%H-"D5.1#I60T%21 T*
`
end
Received on Mon Nov 30 1998 - 23:26:41 CST