| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-13-04, 08:55
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 3
|
|
|
Help with SQL Query
|
|
Hi all,
I have a table as follows:
ID1 ID2 Property1 Property2 Date
1 23 P1 P2 01/04/2004
1 24 P22 P3 02/04/2004
2 25 P13 P22 01/05/2004
2 26 P34 P76 31/01/2004
What I want to retrieve is this:
For each value of ID1, I want to retrieve ONE record from all the ones sharing the same ID1 value, and this record is the earlist record of these.
For example, if I have the data above, I want to retrieve the following result
1 23 P1 P2 01/04/2004
2 26 P34 P76 31/01/2004
Any ideas how to do this? I've tried various GROUP BY and JOINS but can't seem to get anywhere near 
Thanks!
|
|
04-13-04, 10:12
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
|
Re: Help with SQL Query
There are 2 steps involved:
1) What is the earliest date per ID1?
select id1, min(date) from table group by id1;
2) Get the records where the id1 and date values are in the list generated by query 1:
select * from table
where (id1, date) in (select id1, min(date) from table group by id1);
|
|
04-13-04, 11:21
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
|
|
|
Re: Help with SQL Query
|
|
Quote:
Originally posted by andrewst
There are 2 steps involved:
1) What is the earliest date per ID1?
select id1, min(date) from table group by id1;
2) Get the records where the id1 and date values are in the list generated by query 1:
select * from table
where (id1, date) in (select id1, min(date) from table group by id1);
|
Just an observation, but this can produce more than one row per value of ID1 if there are "ties" with the lowest date value.
-PatP
|
|
04-13-04, 11:59
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
and whaddya bet the poster's database won't support the
... where (a,b) in (select x,y...)
structure
(what's that called, anyway? a row expression?)
as far as i know, only oracle does
yeah, it's in sql-92, but since when does every database vendor support sql-92, eh
|
|
04-13-04, 12:16
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,171
|
|
So what is the alternative? I guess maybe:
PHP Code:
select *
from table,
(select id1, min(date) mindate from table group by id1) v
where t.id1 = v.id1
and t.date = v.mindate;
...or is in-line view support unusual too?
|
|
04-13-04, 12:31
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
unusual? kinda
i call that a derived table
mysql certainly would have trouble with it, eh
|
|
04-13-04, 12:36
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
alternatives? two
correlated subquery:
PHP Code:
select ID1
, ID2
, Property1
, Property2
, myDate
from myTable zzz
where myDate =
( select min(myDate)
from myTable
where ID1 = zzz.ID1 )
self-join:
PHP Code:
select t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
from myTable t1
inner
join myTable t2
on t1.ID1 = t2.ID1
group
by t1.ID1
, t1.ID2
, t1.Property1
, t1.Property2
, t1.myDate
having t1.myDate = min(t2.myDate)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with SQL Query
