CODEWITHSUNDEEP
sql-serversalesforce
tonestrike
tonestrike

Reputation: 320

Select max of one column based on two other columns

I have a table with the following data:

id, foreign_id, contact, asset

There are many entries for the same contact with distinct foreign_ids, and what I am trying to do is get the asset value based on max(id).

So let's imagine we have a set of data like this:

id: 1, foreign_id: 15, contact: [email protected], asset: 1
id: 2, foreign_id: 15, contact: [email protected], asset: 2
id: 2, foreign_id: 15, contact: [email protected], asset: 2
id: 3, foreign_id: 14, contact: [email protected], asset: 3
id: 4, foreign_id: 14, contact: [email protected], asset: 1
id: 5, foreign_id: 15, contact: [email protected], asset: 5
id: 6, foreign_id: 15, contact: [email protected], asset: 12

I would like to write a query that returns this:

id: 2, foreign_id: 15, contact: [email protected], asset: 2, share_number: 2
id: 3, foreign_id: 14, contact: [email protected], asset: 3, share_number: 1
id: 4, foreign_id: 14, contact: [email protected], asset: 1, share_number 1
id: 5, foreign_id: 15, contact: [email protected], asset: 12, share_number: 2

As you can see, it gets the max asset for every contact for each foreign_id. Then I also want the share_number of each row with respect to a particular contact ordered by foreign_id.

This is written in Marketing Cloud's version of SQL Server. I cannot use any declarations or variables.

Upvotes: 0

Views: 58

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

One option uses ROW_NUMBER:

SELECT id, foreign_id, contact, asset,
    ROW_NUMBER() OVER (PARTITION BY contact ORDER BY id) rn
FROM
(
    SELECT id, foreign_id, contact, asset,
        ROW_NUMBER() OVER (PARTITION BY foreign_id, contact ORDER BY asset DESC) rn
    FROM yourTable
) t
WHERE t.rn = 1;

Your expected output does not seem to have any particular order, but you can easily impose one by adding a GROUP BY clause to my query.

Edit: I added another call to ROW_NUMBER in the outer query which numbers records sharing the same contact. I assume that the ordering is given by the id column, but I'm not sure about that. Replace ORDER BY id with whatever column you want to use.

Upvotes: 1

Related Questions