|
Home > Archive > Visual FoxPro SQL Queries > March 2006 > Get records with most recent date
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Get records with most recent date
|
|
|
| Hi,
I have a table:
rec_id, data_id, date, some_data
rec_id is primary key, data_id is foreign key, date is datetime type and
some_data is something like description.
I need to get all records with unique data_id where date is most recent.
In other words I want to get the most recent records for every data_id.
I think this is possible to do it in one SELECT, but I don't know how...
I've tried some SELECT's with GROUP BY, HAVING and MAX, but without success.
Can anyone help me?
Thanks.
--
--< pps >--
| |
| Bernhard Sander 2006-03-11, 11:23 am |
| Hi pps,
> I have a table:
> rec_id, data_id, date, some_data
>
> rec_id is primary key, data_id is foreign key, date is datetime type and
> some_data is something like description.
>
> I need to get all records with unique data_id where date is most recent.
> In other words I want to get the most recent records for every data_id.
Try this:
SELECT rec_id, data_id, date, some_data ;
FROM table tabA ;
WHERE a.date = ;
(SELECT max(date) ;
FROM table tabB ;
WHERE tabA.data_id = tabB.data_id )
If there are more than one record with same data_id and same most recent date,
then you will not get unique data_id result records. But your request does not
tell what to do in this case.
May be:
SELECT min(rec_id), data_id, min(date), min(some_data) ;
FROM table tabA ;
WHERE a.date = ;
(SELECT max(date) ;
FROM table tabB ;
WHERE tabA.data_id = tabB.data_id ) ;
GROUP BY data_id
Regards
Bernhard Sander
| |
|
| Bernhard Sander:
> If there are more than one record with same data_id and same most
> recent date,
> then you will not get unique data_id result records. But your request
> does not
> tell what to do in this case.
There won't be the same date for the same data_id in the table, so I don't
even thought about that case.
Anyway, thank you for the solution.
I thought that there exist any SELECT without the second SELECT inside of
it (sorry, I don't know how to tell it in English), but probably I was wrong.
Thanks.
--
--< pps >--
| |
| Bernhard Sander 2006-03-12, 7:23 am |
| Hi pps,
> I thought that there exist any SELECT without the second SELECT inside
> of it (sorry, I don't know how to tell it in English), but probably I
> was wrong.
There is a special term for select inside a select, it is called subselect.
This SQL, in some aspects, even though they tell it is oriented on common
english, is a very logic language. So some of the solutions look very surprising.
And sometimes it is quite hard to find them if you use common sense ;-)
Regards
Bernhard Sander
|
|
|
|
|