Re: Easy SQL question

From: Shatalov Vadim <skiv_at_admkom.tlt.ru>
Date: 1998/12/01
Message-ID: <tpuv37.dui.ln_at_news.admcity.tlt.ru>#1/1

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 !

---

Shatalov Vadim,
Oracle DBA ,Togliatti, Administration Komsomlsk region Phones : +7(8469)245417
mail : skiv_at_admkom.tlt.ru

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 Tue Dec 01 1998 - 00:00:00 CET