Latest Records with associated Data - Query not working 1

[BeachSandGuy]

Hi, I have a data table that has elements like the following:

UniqueRecord | Phone | Type | Status |
101 | 5551111 | Tech | Closed |
102 | 5552222 | CS | Closed |
103 | 5553333 | Support | Pended |
104 | 5551111 | CS | Closed |
105 | 5551111 | Support | Open |
106 | 5553333 | CS | Open |

I want to grab the latest data by unique phone number, the latest having the largest UniqueRecord.
The results should be:

UniqueRecord | Phone | Type | Status |
102 | 5552222 | CS | Closed |
105 | 5551111 | Support | Open |
106 | 5553333 | CS | Open |

So I wrote a query like this:

Select Max(UniqueRecord), Phone, Max(Type), Max(Status) From Table Group By Phone

However I am getting the following:
UniqueRecord | Phone | Type | Status |
102 | 5552222 | CS | Closed |
105 | 5551111 | Tech | Open |
106 | 5553333 | Support | Pended |

I'm sure its due to the way I am using the Max function, which works on the right UniqueRecord and Phone, but the other fields which I just want to bring over, I can't use "Last" like I have done in the past in Access. Max seems to look for the alphabetically largest value and not the value related to the unique record/phone. How can I write this to get the results I am looking for? I've been looking and just can't seem to figure it out. Thanks!

BeachSandGuy

 

[gk53]

this is your solution...

declare @Table as table
(
UniqueRecord int,
Phone int,
[Type] varchar(20),
[Status] varchar(20)
)
insert into @Table
select 101, 5551111,'Tech', 'Closed'
union
select 102, 5552222,'CS', 'Closed'
union
select 103, 5553333,'Support', 'Pended'
union
select 104, 5551111,'CS', 'Closed'
union
select 105, 5551111,'Support', 'Open'
union
select 106, 5553333,'CS', 'Open'


select t.*
from @Table t
	join (
		select phone, recid = MAX(UniqueRecord)
		from @Table
		group by phone
	) u
	on u.recid = t.UniqueRecord
order by t.UniqueRecord

 

[bborissov]

DECLARE @test TABLE (UniqueRecord int, Phone int, Type char(10), Status char(10))
INSERT INTO @Test VALUES(101, 5551111, 'Tech', 'Closed')
INSERT INTO @Test VALUES(102, 5552222, 'CS', 'Closed')
INSERT INTO @Test VALUES(103, 5553333, 'Support', 'Pended')
INSERT INTO @Test VALUES(104, 5551111, 'CS', 'Closed')
INSERT INTO @Test VALUES(105, 5551111, 'Support', 'Open')
INSERT INTO @Test VALUES(106, 5553333, 'CS', 'Open')


;with cte as (select UniqueRecord, Phone, Type, Status,
                     row_number() over (partition by Phone order by UniqueRecord DESC) as RcNo
               from @test)


SELECT  UniqueRecord, Phone, Type, Status FROM cte WHERE RcNo = 1 order by UniqueRecord

Borislav Borissov
VFP9 SP2, SQL Server

 

[BeachSandGuy]

Thank you gh53!!! Worked like a charm.

BeachSandGuy