CODEWITHSUNDEEP
sqlms-access
Dan
Dan

Reputation: 75

SQL Query help for Grouping Results

I have an Access table with the following format:

CostomerID, PhoneType, LastUsedDate, PhoneNumber

I want to write a query that will give me the most recent phone number for each customer by type. So let's say we have

CostomerID, PhoneType, LastUsedDate, PhoneNumber
987/Landline/201809/555-343-9017
987/Landline/201610/555-397-0975
987/Cell/201811/555-870-1862

I want the query to generate results for Customer 987, with his landline number from 201809 and his cell number from 201811. So far I have come up with

SELECT  p.CnBio_ID, p.Type, max(p.Subj_Date_Last_Seen_1) as maxdate
  from Phones p
  GROUP BY p.CnBio_ID, p.Personal_Business, p.Type

This generates the most recent date per ID and type, but I can't figure out how to add the phone number that corresponds to that data into the results?

Edit: I should add that each row also has it's unique identifier, if that would help.

Upvotes: 0

Views: 39

Answers (2)

Shadiqur
Shadiqur

Reputation: 492

You can use join like :

 SELECT t1.* ,t2.PhoneNumber(SELECT  p.CnBio_ID, p.Type, max(p.Subj_Date_Last_Seen_1) as axdate,p2.phonenumber
      from Phones p  GROUP BY p.CnBio_ID, p.Personal_Business, p.Type) as t1 inner join Phones as t2 on t1.CnBio_ID = t2.CnBio_ID;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try with correlated subquery

SELECT  p.CnBio_ID, p.Type, p.Subj_Date_Last_Seen_1 as maxdate
from Phones p
where p.Subj_Date_Last_Seen_1 in (select max(p.Subj_Date_Last_Seen_1) from phones p1 
where p1.CnBio_ID=p.CnBio_ID and p1.Type=p.Type group by p1.CnBio_ID, p1.Type)

OR You can try using row_number()

select * from
(
SELECT  p.CnBio_ID, p.Type, p.Subj_Date_Last_Seen_1,row_number() over(partition by  p.CnBio_ID, p.Type order by p.Subj_Date_Last_Seen_1 desc) as rn
from Phones p
)A where rn=1

Upvotes: 1

Related Questions