Getting max(date) and relevant unique GUID

Question

Getting max(date) and relevant unique GUID

woody_rd

Ten Centuries

Points: 1012
More actions
April 4, 2017 at 2:08 pm

#329105

I have a table with a uniqueidentifier column (36 char GUID), an associated customerid column, and a date field.
I need to get the most recent uniqueidentifier and date for the customerid, but when I try to do so I'm getting ALL GUID's associated with the customer, regardless of MAX(Date).
Here's what I'm trying:
SELECT id, cust_id, MAX (IIF(CompDate IS NULL, 0, CompDate)) AS MaxDate
FROM Table1
where
cust_id = <SOME_GUID>
GROUP BY Id, cust_id
So since I'm specifiying a cust_id and getting the max(compdate), I'd expect 1 row... but that's not what I get. What am I doing wrong?
Many thanks in advance...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

maybe using row_number will get what you are after?
With LatestGUIDBasedOnCompDate AS ( SELECT Row_number() OVER(Partition BY ID ORDER BY ISNULL(CompDate,'1900-01-01') DESC) AS RW, ID, CUST_ID, CompDate FROM Table1 ) SELECT * FROM LatestGUIDBasedOnCompDate T2 WHERE RW = 1 --Rownumber alias RW is the "Latest"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Or another thought:

SELECT TOP 1 id, cust_id, ISNULL(CompDate, '1900-01-01') AS MaxDate FROM Table1 WHERE cust_id = <SOME_GUID> ORDER BY CompDate DESC

You don't need to do any grouping. All you want is the id, cust_id and CompDate where CompDate is Max(CompDate). The above query will give you that and the "TOP 1" will ensure you only get the first record. TOP is nice, but you do need to be careful with it. I've seen it used without an ORDER BY and then you have no idea what order things are coming out in.
Threw the ISNULL check in there because I can't remember the default sorting for NULL values when you do DESC ordering. It might not be required, but I find it is always better to give your NULLs a value when doing any work in SQL to prevent unexpected results. NULL != NULL afterall...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

woody_rd Ten Centuries Points: 1012 More actions · Answer 3

Thank, Guys. I'll give both a shot. Side note: I'm using a single cust_id to validate my data... would both of these solutions work with multiple cust_id's?

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

My version will not work with multiple customers. If you want to use multiple customers, the CTE method mentioned by Lowell is the way to go.

Just working out if there is a better way to do it... a self join would probably work...

EDIT: Self join method:
SELECT DISTINCT id, cust_id, CompDate FROM Table1 JOIN (SELECT DISTINCT id, MAX(CompDate) OVER (PARTITION BY Cust_ID) as MaxDate FROM Table1) max ON max.id = Table1.id AND max.MaxDate = Table1.CompDate

I'm not sure if the CTE or the self join will be faster or less resource heavy, but I am fairly confident that that should work. The join is getting the id and max date partitioned by cust_id's. So the join should give you an ID and a Max Date with 1 row per customer. This is then compared to the full data set Table1 on the ID (which I am assuming is unique) and the CompDate.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.