Re: Easy SQL question

That's why Oracle provided sub-selects. First turn your query that gives you the most recent date into a view by surrounding it with parenthesis. Then wrap another select around it that uses the result of the first in the where clause. For example:

I suppose this is the select that gives you the most recent date:

select name, max(date) from x;

Now be sure to 'rename' the result of max(date) .... like so:

select name, max(date) max_date from x;

In this particular example, this rename doesn't turn out to be absolutely necessary, but it is a good practice to get into. Next, surround that select with parenthesis:

(select name, max(date) max_date from x) ;

Now wrap another select around that first one, putting the result of the first select as the criteria of the WHERE clause of the one you are adding:

select name, age, weight, date from x
  where (name, date) in
(select name, max(date) max_date from x) ;

And there you have it!

wbailey_at_my-dejanews.com wrote:

> 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?
>
> Thanks for the help,
> Bill
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Sun Nov 29 1998 - 00:00:00 CST