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

Improve this question
0

2 Answers 2

Reset to default
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.

Improve this answer
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.
    – Juraj Resko
    Commented Nov 22, 2019 at 10:20
  • Do you only post when you can plug your product?
    – AnonWonderer
    Commented Nov 22, 2019 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
    – Jonathan Van Driessen
    Commented Nov 29, 2019 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! :)
    – Juraj Resko
    Commented Dec 10, 2019 at 15:35
  • Thanks for sharing @JurajResko!
    – Jonathan Van Driessen
    Commented Dec 12, 2019 at 6:01
0

You could do with Sort and Limit:

SELECT TOP 1 GlobalID, CategoryDescription, MAX 
FROM Your_Data_Extension 
ORDER BY MAX DESC
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .