trouble with non distinct records 1

[Larrabbb]

I am having some problems filtering some records that aren't distinct. Here is my scenario:

Example record set in this format:

MessageID, ReceiveDate, ActionDate1, ActionDate2, SendDate, Name, Code, Description

Here is 6 records from the table I am pulling from. Notice some have same MessageID, the only difference is a different ActionDate1. See below for question.

82285 2003-06-12 19:49:34 2003-06-15 08:48:53 2003-06-15 11:33:26 2003-06-15 11:33:26 XYZName Code Description

82285 2003-06-12 19:49:34 2003-06-14 15:12:17 2003-06-15 11:33:26 2003-06-15 11:33:26 XYZName Code Description

82285 2003-06-12 19:49:34 2003-06-15 07:24:23 2003-06-15 11:33:26 2003-06-15 11:33:26 XYZName Code Description

82301 2003-06-12 20:00:01 2003-06-14 15:12:17 2003-06-15 09:47:09 2003-06-15 09:47:09 XYZName Code Description

82301 2003-06-12 20:00:01 2003-06-15 08:52:07 2003-06-15 09:47:09 2003-06-15 09:47:09 XYZName Code Description

82302 2003-06-12 20:00:02 2003-06-15 08:52:07 2003-06-15 11:39:27 2003-06-15 11:39:27 XYZName Code Description

Because ActionDate1 can change on the same messageID, it creates multiple records with the same messageID. What I want to do, is return only records with distinct messageIDs. And, for those records that have the same messageID, I want to return the record that has the most recent ActionDate1.

Can anyone recommend a way to do this? I am thinking I am going to have to use a cursor, but am not completely clear on the logic that will have to be used in it.

Thanks

 

[rac2]

I would create a view containing only the rows with the most recent ActionDate1. Some would suggest a subquery. I like views because you may want to use these rows in other contexts in the future and because it makes the resulting query easier to write and to understand.

CREATE VIEW LatestActions AS
SELECT MessageID,
       MAX(ActionDate1) AS ActionDateLast
FROM Actions
GROUP BY MessageID

Then JOIN the base table and the view using ActionDateLast instead of ActionDate1.

SELECT a.MessageID,
       b.ActionDateLast,
       a. etcetera
FROM Actions a
JOIN LatestActions b ON a.MessageID = b.MessageID

Because this is an inner join, only the rows in LatestActions will be in the result; and since every MessageID has at least one ActionDate1, (right?), every MessageID will be in the result.

 

[Larrabbb]

OK, that helped. Here is what I used:

SELECT distinct(a.MessageID),
b.ActionDateLast,
a. etcetera
FROM Actions a
JOIN LatestActions b ON a.MessageID = b.MessageID

now my problem is this...take a look back at the original record set. In that recordset, there is a field that is called 'CODE'. Well, in some instances, there is a different code attached to the messageID from the MIN(ActionDate1) to the MAX(ActionDate1). So in my a.etcetera group, I need to include a.CODE, which makes me group by a.CODE. When I do this, it pulls in both CODES, thus causing 2 messageID's to still be present.

So, I want to pull into the view the CODE as well, but only the CODE from the record where the MAX(ActionDate1) resides, and then when I do my JOIN, I will do something like this:

SELECT a.MessageID,
b.ActionDateLast,
b.CODE,
a.etcetera
FROM Actions a
JOIN LatestActions b ON a.MessageID = b.MessageID

Thoughts on how to do this?

 

[Bygbobbo]

I think this will give you what you need...

A correlated subquery using your original table..

Scrap the view it's not neccessary...


select *
from actions a
where 0 = 0
and actiondate1 in (select max(ActionDate1) from actions b where b.messageID = a.messageID)

you can add b.code = a.code in the subquery also to pull out the min and max by a specific code:

select *
from actions a
where 0 = 0
and actiondate1 in (select max(ActionDate1) from actions b where b.messageID = a.messageID and b.code = a.code)


I think this is what you are trying to do???

hope this helps,
Bygs

 

[JamesLean]

I would create a view like this:

CREATE VIEW DistinctMessage
AS

SELECT *
FROM mytable t1
WHERE actiondate1 = (
  SELECT MAX(actiondate1) FROM mytable
  WHERE messageid = t1.messageid
)

This will give you a row for each distinct messageid, taking the entire row from the one with the max actiondate1. It doesn't matter about whether any other columns can change or not.

--James

 

[Bygbobbo]

LOL,

JamesLean what is different about what I wrote and you wrote.

Except that you re-iterated my query...

 

[Larrabbb]

Thanks guys for your help. There are still a few records that are being returned that aren't unique(see my last post), but after some thought, I've decided that returning those records is OK for now.

I may be back on here again in a month or two when I have to run some different queries using this same data.

Cheers!

 

[rac2]

All of the columns can be from Actions. The purpose of the view is to identify the row with the most recent ActionDate1. My first post should have had a second condition in the JOIN.

If you are still getting multiple rows from this query it means that (MessageID, ActionDate1) is not unique in the Actions table. If these two values do not uniquely identify a row, what column can be added so that (MessageID, ActionDate1, col3) is unique?

It is lucky you want the CODE with the lastest ActionDate1.

SELECT a.MessageID,
       a.ActionDateLast,
       a.CODE,
       a. etcetera
FROM Actions a
JOIN LatestActions b ON a.MessageID = b.MessageID
                    AND a.ActionDate1 = b.ActionDateLast