CODEWITHSUNDEEP
t-sql
NihilisticWonder
NihilisticWonder

Reputation: 3

T-SQL select distinct based on highest values from another column

I have a table that contains number of tryouts, customerID, status of that one tryout and some other columns with various data.

Of course a single customerID can have multiple number of tryouts ( in the real table first tryout is number 1, second one number 2 etc.).

Ex.

  • Customer ID = 1, tryout = 1
  • Customer ID = 1, tryout = 2
  • Customer ID = 1, tryout = 3
  • Customer ID = 2, tryout = 1
  • Customer ID = 3, tryout = 1
  • Customer ID = 3, tryout = 2

And I want to have all distinct customerIDs but for each one only the row, that contains the highest tryout number for each customer in one table with data from all the other columns as well. Ex. tryouts, customerID, status, data1, data2

How can I achieve that ?

Upvotes: 0

Views: 50

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

If you only want the customer ID and tryout value then you can try the following:

SELECT customerID, MAX(tryout) AS max_tryout
FROM yourTable
GROUP BY customerID

If you want the entire record, then one option would be to use ROW_NUMBER():

SELECT t.customerID, t.tryout, t.status, t.data1, t.data2
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY tryout DESC) rn
) t
WHERE t.rn = 1

Upvotes: 1

Peter Smith
Peter Smith

Reputation: 5550

Try

SELECT
    CustomerID,
    MAX(tryout) AS [Max tryout]
FROM
    TheTable
GROUP BY
   CustomerID

This should give you what you want

Upvotes: 0

Related Questions