Show latest Entry with Group By and Where Clause

Transact-SQL
1

I have 1 table that has the following columns

id
clientid
ref
status
agent

From this table I want to
a) group the records by clientid
b) get the latest entry where ref = 3 (the latest entry can be got if you run a max on the id column)

so if I ran with the records below then the results will be just 1 entry that is highlighted.

image

2 
SELECT tn.id, tn.clientid, tn.ref, tn.status, tn.agent
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY id DESC) AS row_num
    FROM dbo.table_name
    WHERE ref = 3
) AS tn
WHERE tn.row_num = 1
3

Great that works Scott. You are a star.

I need to now add another table that only has a column clientid

I want table 2 to do an inner join to those of the query you just gave based on the clientid but only show those that have a status of 'in' please.

4

Where the last status is 'in' or where any of the status'es was in?

If just any status needs to be 'in', then 111 would be chosen (id 1 = 'in' even though it's not the last statu

If the last status needs to be 'in', then 111 would not be chosen. For now, I'll assume that is what you want.

SELECT tn.id, tn.clientid, tn.ref, tn.status, tn.agent
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY id DESC) AS row_num
    FROM dbo.table_name tn
    WHERE ref = 3 AND
	    EXISTS(SELECT 1 FROM dbo.table2 t2 WHERE t2.clientid = tn.clientid)
) AS tn
WHERE tn.row_num = 1 AND tn.status = 'in'
5

Hi Scott Thanks it was perfect the task done. Sorry I did not reply earlier as I was away on an Easter Break. Thanks once again you are a star