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.
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
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.
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'
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