Hello,
I am trying to write a query that will return only the most recent results. The table I am pulling information from has no uniqiue columns, and contains information on rate changes so for any particular client there can be several rate changes - I only want the most recent rate change for each client.
The structure is:
mrmatter VARCHAR(14)
mrtk VARCHAR(14)
mreffdate DATETIME
mrtitle VARCHAR(100)
mrrate INT
mrdevper INT
Some sample data is:
mrmatter mrtk mreffdate mrtitle mrrate mrdevper
184-00111 0005 2001-03-19 00:00:00.000 ! 250 NULL
184-00111 0259 2001-03-19 00:00:00.000 ! 220 NULL
184-00111 9210 2001-03-19 00:00:00.000 ! 220 NULL
184-00111 0005 2007-07-01 00:00:00.000 ! NULL NULL
From the data above you can see there is two mrtk (0005), from these results it should only return three instead of the four rows.
The query isnt just on mrtk, instead of mrtk there could be a mrtitle in which case I would need to find the most recent date, when there is multiples.
I have tried the following query, it returns the results sorted in newest to oldest, but it returns four rows (two 0005) instead of only the three. I have tried different ways of doing the same query but it all returns the same results.
SELECT mrmatter,mrtk,mrrate,MAX(mreffdate) AS 'MostRecent'
FROM mexrate
WHERE mrmatter='184866-00111'
GROUP BY mrmatter,mrtk,mrrate
Any assistance that can be provided would be greatly appreciated.
UPDATE: The mrrate column can contain nulls, and the nulls can be the most recent entry. What I am after is the most recent entry for the same mrmatter AND (mrtk OR mrtitle).
Some more sample data is:
mrmatter mrtk mrtk mrrate mreffdate
100626-01406 Senior Assoc ! 235.000 2006-01-25 00:00:00.000
100626-01406 Solicitor ! 235.000 2006-01-25 00:00:00.000
100626-01407 Associate ! 265.000 2006-01-30 00:00:00.000
100626-01407 Associate ! 276.000 2007-07-01 00:00:00.000
100626-01407 Partner ! 265.000 2006-01-30 00:00:00.000
100626-01407 Partner ! 276.000 2007-07-01 00:00:00.000
100626-01407 Senior Assoc ! 265.000 2006-01-30 00:00:00.000
100626-01407 Senior Assoc ! 276.000 2007-07-01 00:00:00.000
Matt