CODEWITHSUNDEEP
sqlt-sql
Pedro
Pedro

Reputation: 63

Query that Gets all data when an ID is the same

I have a table that returns me these results.

ProcessID   AddressID   ItemStateID
22             2            50
22             3            50
22             4            50
22             5            50
22             6            50
22             7            60
22             7            50
22             8            50
22             9            50
22             10           60
22             10           50
22             11           50
22             12           50

But what i want is this:

ProcessID   AddressID   ItemStateID
22             2            50
22             3            50
22             4            50
22             5            50
22             6            50
22             7            60
22             8            50
22             9            50
22             10           60
22             11           50
22             12           50

When the AddressID is the same i want to get the highest ItemStateID in one row...

For now this is the query i am using at the moment:

SELECT DISTINCT
    ProcessID,
    AddressID,
    ItemStateID
FROM MMResult2
WHERE ProcessID = 22 AND ItemStateID > 10
ORDER BY AddressID ASC, ItemStateID DESC

Upvotes: 0

Views: 46

Answers (4)

Roman Marusyk
Roman Marusyk

Reputation: 24589

SELECT 
    ProcessID,
    AddressID,
    MAX(ItemStateID)  AS ItemStateID 
FROM MMResult2
WHERE ProcessID = 22 AND ItemStateID > 10
GROUP BY ProcessID, AddressID
ORDER BY AddressID ASC, ItemStateID DESC

Upvotes: 0

Matt
Matt

Reputation: 15061

Use a GROUP BY and MAX Function.

SELECT DISTINCT ProcessID, AddressID, MAX(ItemStateID) AS HighestItemStateID
FROM MMResult2
WHERE ProcessID = 22 AND ItemStateID > 10
GROUP BY ProcessID, AddressID, 
ORDER BY AddressID ASC, ItemStateID DESC

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a GROUP BY:

SELECT 
    ProcessID,
    AddressID,
    MAX(ItemStateID)
FROM MMResult2
WHERE ProcessID = 22 AND ItemStateID > 10
GROUP BY AddressID

Upvotes: 0

juergen d
juergen d

Reputation: 204794

Remove distinct and group by the columns you want to be unique. To aggregate the last column use max()

SELECT ProcessID,
       AddressID,
       max(ItemStateID) as MaxItemStateID
FROM MMResult2
WHERE ProcessID = 22 
  AND ItemStateID > 10
GROUP BY ProcessID,
         AddressID
ORDER BY AddressID ASC, 
         max(ItemStateID) DESC

Upvotes: 2

Related Questions