CODEWITHSUNDEEP
sqlsql-server
test
test

Reputation: 11

Picking one of many identical rows with certain condition

To set the scene, what I define as identical rows are when the combination of destination and vehicle_brand are the same. For instance in the figure below,

SQL table name: cardriven

rows 2 and 3 are "identical" because of the Dallas-Toyota "combination." Now I want to only display the row with the higher request_id. So for example, between rows 2 and 3, row 3 would get displayed and row 2 would be hidden/removed because 169 > 100. So in the end, only rows 3, 4, 5, 7, and 8 will show and rows 1, 2, 6, and 9 would get hidden/removed.

Hopefully you understand what I am going for here but if you have any questions, please let me know. This will be written in SQL code.

Another problem: I added a new column for dates and entered some random ones for rows 2-4. Row 2 is 12/1/17, row 3 is 11/5/2016, and row 4 is 7/6/2017. Note that row 3 has the highest request_id out of the Dallas-Toyota combination. I decided to enter a new entry in with a request_id = 501 and entry of Dallas, Toyota, and 12/22/2017. After running the program, for Dallas-Toyota I return row 3 but with request_id = 501! It SHOULD return the entry I just entered.

Upvotes: 1

Views: 36

Answers (1)

Wayne Allen
Wayne Allen

Reputation: 1735

You can use Group By and the Max function to get the highest value.

SELECT MAX(request_id), destination, vehicle_brand
FROM cardriven
GROUP BY destination, vehicle_brand

Upvotes: 1

Related Questions