Help with SQL

[Darryl Kerkeslager]

Table: address

address_id (PK, autonumber)
add_off_id (FK, Long, has duplicates)
add_start_date (date, ver likely to have duplicates)
add_current (Boolean)

Sample Data:

1 256 1/1/2004 False
2 256 12/1/2005 False
3 480 1/1/2004 False
4 550 1/2/2005 False
5 980 4/7/2005 False
6 998 4/7/2005 False
7 998 4/9/2005 False
8 1200 1/1/2003 False
9 1230 1/1/2003 False

I want to set add_current to True for address #2 and #7, because they have
the same
add_off_id, and the add_start_date is the newest of the add_start_dates for
this add_off_id.


--
Darryl Kerkeslager

[DFS]

Darryl Kerkeslager wrote:

Table: address

address_id (PK, autonumber)
add_off_id (FK, Long, has duplicates)
add_start_date (date, ver likely to have duplicates)
add_current (Boolean)

Sample Data:

1 256 1/1/2004 False
2 256 12/1/2005 False
3 480 1/1/2004 False
4 550 1/2/2005 False
5 980 4/7/2005 False
6 998 4/7/2005 False
7 998 4/9/2005 False
8 1200 1/1/2003 False
9 1230 1/1/2003 False

I want to set add_current to True for address #2 and #7, because they
have the same
add_off_id, and the add_start_date is the newest of the
add_start_dates for this add_off_id.

Why just those two addresses? The following will do what you want. If you
take out the HAVING clause it will update all records where the Date is the
latest date for that add_off_id.

UPDATE Table1 A
SET A.ADD_CURRENT = True
WHERE A.ID IN
(SELECT ID
FROM TABLE1 B INNER JOIN
(SELECT FK, Max(DT) AS MaxOfDT
FROM Table1
GROUP BY Table1.FK
HAVING COUNT(FK) > 1) C
ON B.FK = C.FK
AND B.DT = C.MAXOFDT);

address_id = ID
add_off_id = FK
add_start_date = DT
add_current = ADD_CURRENT
Also, there are people in the c.d.oracle.misc and the c.d.ms-sqlserver
groups who are very good with SQL statements, so you might crosspost there.

[Darryl Kerkeslager]

Yes, PERFECT! Thank you, very much! This kind of complex SQL makes my head
spin round 23 times and spew pea soup.

Why just those two addresses? The following will do what you want. If
you
take out the HAVING clause it will update all records where the Date is
the
latest date for that add_off_id.
I thought about that AFTER I posted, but figured if you gave me the hard
part, changing the single addresses was trivial. But thanks for catching my
posting error, too.
Also, there are people in the c.d.oracle.misc and the c.d.ms-sqlserver
groups who are very good with SQL statements, so you might crosspost
there.

Ack! Ptooey. No need.

Thanks, again.

Darryl Kerkeslager

[DFS]

Darryl Kerkeslager wrote:

Yes, PERFECT! Thank you, very much! This kind of complex SQL makes
my head spin round 23 times and spew pea soup.
ha!

Well, it's kind of entertaining to work on [sometimes], and a single query,
even if relatively complex (which that one I wrote isn't), is almost always
better than a VBA routine. It usually runs quicker, and is easier to
maintain, and is more portable.

Why just those two addresses? The following will do what you want.
If you
take out the HAVING clause it will update all records where the Date
is the
latest date for that add_off_id.

I thought about that AFTER I posted, but figured if you gave me the
hard part, changing the single addresses was trivial. But thanks for
catching my posting error, too.

Also, there are people in the c.d.oracle.misc and the
c.d.ms-sqlserver groups who are very good with SQL statements, so
you might crosspost there.

Ack! Ptooey. No need.

heh... never met an Access developer who hated SQL so much. But just for
fun - since I want to see your head spin around, here's something I found on
cdom:
SELECT mt1.StartKey || mt1.ID "Begin"
, mt2.StartKey || mt2.ID "End"
FROM (SELECT ROWNUM TheRowNum
, t.*
FROM (SELECT ID
, StartKey
, theDate
FROM MyTest
WHERE StartKey = 'B'
ORDER BY theDate) t) mt1
, (SELECT ROWNUM theRowNum
, t2.*
FROM (SELECT t.*
, QueryUtility_PKG.KeepEndRow(ROWNUM, t.theDate)
KeepRow
FROM (SELECT ID
, StartKey
, theDate
FROM MyTest
WHERE StartKey = 'E'
ORDER BY theDate) t) t2
WHERE KeepRow = 1) mt2
WHERE mt1.theRowNum = mt2.theRowNum(+)
ORDER BY mt1.theRowNum;

Enjoy!


Thanks, again.

Darryl Kerkeslager

[Darryl Kerkeslager]

Eeeeewwww!