1

I am in the middle of creating an SQL in SFMC. I have three attributes in the source Data Extension:

  • GlobalID,

  • CategoryDescription

  • MAX (yes, the name is weird, but it is not the point...).

What I want to achieve is to move to the target data extension all three attributes, but only the record with the highest MAX attribute value (128614.150). It is the last record (see the photo attached).

So the target DE would have only one record:

GlobalID, CategoryDescription, MAX

1000047634, Category 8, 128614.150

Thank you very much for your help!

to

0
0

You didn't say what the name of your data extension is, I'm going to assume it's DataExtension1. I think this should work:

SELECT GlobalID, CategoryDescription, MAX 
FROM DataExtension1 
WHERE MAX = (SELECT Max(MAX) FROM DataExtension1)  

Note that with this statement you would get 2 records if there were 2 rows with the same MAX value.

I hope that helps.

PS: Note there are tools out there like deselect.io that help you build this kind of difficult selections without having to write an SQL query.

5
  • Hello Jonathan Thank you for your reply. My additional question is. What, If I have more GlobalID attributes and I want to see the highest category value for all GlobalIDs? Would it be possible? The result would be something like this: Record 1) 1000047634, Category 8, 128614.150; Record 2) 1000047659, Category 5, 3893.000; Record 3) 1000047678, Category 6, 7243.24 Many thanks. Nov 22 '19 at 10:20
  • Do you only post when you can plug your product? Nov 22 '19 at 23:27
  • Juraj, I've read your comment 5 times and still not 100% sure I understand correctly :) Is your question to get the record with the highest MAX for each GlobalID? In that case I think this is what you need: SELECT GlobalID, CategoryDescription, MAX FROM (SELECT GlobalID, Max(MAX) AS Max FROM DataExtension1 GROUP BY GlobalID) AS M INNER JOIN DataExtension1 ON DataExtension1.GlobalID = M.GlobalID AND DataExtension1.Max = M.Max In case you wouldn't need the CategoryDescription it would be a lot easier: SELECT GlobalID, Max(MAX) AS Max FROM DataExtension1 GROUP BY GlobalID Nov 29 '19 at 8:33
  • Hello @JonathanVanDriessen. I needed to do some kind of workaround for this. I had to use a rownum function for this case. To create a rank attribute for all the "MAX" values and then to filter the highest value that was equal to the rank number. Thank you though! :) Dec 10 '19 at 15:35
  • Thanks for sharing @JurajResko! Dec 12 '19 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.