Need Your Guys Help With A Query

[The Pig]

Got a table.

Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc

The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.

Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc

Any help would be appreciated.

The Pig
8675309

[Marc]

ia*******@yahoo .com (The Pig) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

Got a table.

Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc

The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.

Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc

Any help would be appreciated.

The Pig
8675309

L.S.,

Do you really have to delete the rows or is filtering them out is an
option ?
Filtering out is the simplest method:
when in design-view click the totals-button (the greek E-like symbol)
and select the max-function in stad of the group-by function on the
total-line for the time-column. The resulting query will give what you
want. The resulting query however is not updatable but perfect for
other use without deleting the table-data.
You also could save the result by changing the querytype to create a
new table through the query-menu.

Marc
Marc

[Pink Panther]

Piggy,

Is there a Primary Key on the table? Some way to uniquely identify a
record like a unique constraint?

Otherwise how does the db 'know' which of the same records to delete?

Given you have a PK make 2 queries

SELECT Min(CDT2.PK) AS MinOfPK, CDT2.Code, CLng([DateTime]) AS DateOf
FROM CodeDateTime AS CDT2
WHERE (((CDT2.DateTim e)=(SELECT Max(CDT3.DateTi me) FROM CodeDateTime as
CDT3 WHERE clng(CDT3.DateT ime) = clng(CDT2.DateT ime))))
GROUP BY CDT2.Code, CLng([DateTime]);

I've called it qselPKSToKeep. This will 'arbitarily' select one of the
PK's too keep if the datetime and code are same (the lowest number but it
could be the max, it doesn't matter)

Second Query (the 'outer' query)

DELETE CodeDateTime.PK , CodeDateTime.Da teTime, CodeDateTime.Co de
FROM CodeDateTime
WHERE (((CodeDateTime .PK) Not In (SELECT PTK.MinOfPK FROM qselPKSToKeep
as PTK)));

There may be a simpler way but I certainly can't think of it now....

Peter


ia*******@yahoo .com (The Pig) wrote in
news:81******** *************** ***@posting.goo gle.com:

Got a table.

Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc

The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.

Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc

Any help would be appreciated.

The Pig
8675309

[MeadeR]

Here you go:

Delete *
From test as t
Where (Select Max(Time)
From Test Where test.code = t.code and test.date = t.date) > t.time

This would not handle the problem where you have two records with the
exact same time....

ia*******@yahoo .com (The Pig) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

Got a table.

Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc

The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.

Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc

Any help would be appreciated.

The Pig
8675309

[Xiaolu]

ia*******@yahoo .com (The Pig) wrote in message news:<81******* *************** ****@posting.go ogle.com>...

Got a table.

Date Time Code
1/1/2003 1700 xbc
1/1/2003 1800 xbc
1/1/2003 1800 xbc
2/4/2004 1650 abc
2/4/2004 1700 abc

The problem is I need a query that will delete rows that have the same
"Code" and same "date" but will keep the line that has the higher
value in in the time field. Sometimes there are rows that have one
time that is lower and one that is higher, sometimes there can be 20
lines that have different times but the same higher value (ie. first
three lines of sample table above). I'm lost and have been banging my
head for the past two days. The finish table would looke like this.

Date Time Code
1/1/2003 1800 xbc
2/4/2004 1700 abc

Any help would be appreciated.

The Pig
8675309

If the data type for Time is number, I think the following code works.
SELECT Date, MAX(Time) AS MaxOfTime, Code
FROM tblYourTable
GROUP BY Date, Code;

Xiaolu

[jeff colton]

You can do this by creating a temporary table of the records you want.
Then delete all of the records in the base table and repopulate it
with the wanted records. Here are the queries you can use to do this.

First select the wanted records into a temporary table:

SELECT date, Max(time) AS maxtime, code INTO temp
FROM Table2
GROUP BY date, code;
Then delete all of the records in the base table:

DELETE *
FROM Table2;

Finally repopulate the base table with the stored records:

INSERT INTO Table2
SELECT temp.*
FROM temp;
Hope this helps.

Jeff