CODEWITHSUNDEEP
sqlt-sql
Ugur_irk
Ugur_irk

Reputation: 1

How can I make selection based on conditions on SQL?

There is a table based on ID an those ID's status keys:

The table

I need to write query that will bring higher status key of the same ID. For example; query will bring only the row with status key number 9 for ID number 123. But it will bring the row with status key number 2 for ID number 156.

Hope I managed to explain myself clearly. Please help me with this query.

Upvotes: 0

Views: 50

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Use max() aggregation

select id, max(status_key)
from tablename
group by id

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23797

You didn't tag your backend, this would work with many backends and older versions of many backends (assuming you have other columns too in your table - otherwise do only group by):

select myTable.* 
from myTable 
inner join 
(select id, max(statusKey) as statusKey
from myTable
group by id) tmp on myTable.id = tmp.id and myTable.statusKey = tmp.statusKey;

Upvotes: 0

Related Questions