How would you do this?
-
I've got a bit of a problem with a query I am trying to write...
I have a table with the following fields:
- contactd_id
- contactd_contactmid
- contactd_status
- contactd_email
And here is some example data from this table:
contactd_id contactd_contactmid contactd_status contactd_email
----------- ------------------- --------------- ----------------
There will only ever be at most two records with the same 'contactd_contactmid' in the table. If there are two records with the same 'contactd_contactmid' I would only like to select the one with the highest 'contactd_status'. The 'contactd_status' will always be different and range between 1 - 4.
I'm sure this is fairly basic but I just can't think how to do it! If you can help me out it would be greatly appreciated.
Thanks in advance...
-
Not tested, just written....
SELECT *
FROM [ContactTable] CT
WHERE contactd_id IN (
SELECT contactd_id
FROM [ContactTable] BestRecsID
JOIN (
SELECT contactd_contactmid, MAX(contactd_status) as High_contactd_status
FROM [ContactTable]
GROUP BY contactd_contactmid
) GetHigh --
ON BestRecsID.contactd_contactmid = GetHigh.contactd_contactmid
AND = BestRecsID.contactd_status = GetHigh.High_contactd_status)
Once you understand the BITs, all the pieces come together
-
select a.contactd_id,a.contactd_contactmid,
a.contactd_status,a.contactd_email
from test1 a
where a.contactd_status in (Select max(contactd_status)
from test1 b
where b.contactd_contactmid=a.contactd_contactmid)
-
To answer the question from the subject line...
SELECT c.ContactD_Id, c.ContactD_ContactMid, c.ContactD_Status, c.ContactD_Email
FROM Contacts c JOIN
(SELECT ContactD_ContactMid, MAX(ContactD_Status) ContactD_Status
FROM Contacts
GROUP BY ContactD_ContactMid) x ON x.ContactD_ContactMid = c.ContactD_ContactMid AND x.ContactD_Status = c.ContactD_Status
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply