CODEWITHSUNDEEP
sqlmax
user2907652
user2907652

Reputation: 13

MAX Record for specic row results

I have a table with the following data.

ITEM          LOCATION          ItemKey  
5551212       A                 5  
5551212       B                 4  
5551212       C                 3  
5553434       A                 2  
5553434       B                 1

What I would like is to be able to query this data and return another column where the value is T if the ItemKey is = Max(ItemKey) for each item and an F it is not. So for example the results would look like this.

ITEM          LOCATION          ItemKey          NEWFIELD  
5551212       A                 5                T  
5551212       B                 4                F  
5551212       C                 3                F  
5553434       A                 2                T  
5553434       B                 1                F

Any ideas?

Thank you

Upvotes: 0

Views: 66

Answers (2)

jpw
jpw

Reputation: 44891

Maybe something like this would work:

SELECT 
  t1.*, 
  CASE t1.ItemKey WHEN t2.MaxItem THEN 'T' ELSE 'F' END AS NEWFIELD 
FROM table_name t1
LEFT JOIN (
  SELECT item, MAX(ItemKey) AS MaxItem 
  FROM table_name
  GROUP BY item
) t2 ON t1.ITEM = t2.ITEM

It might not be the most efficient way to solve the problem though.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460208

If you want to set it to T If it belongs to the location that has the Max(ItemKey):

SELECT t1.ITEM, t1.LOCATION, t1.ItemKey, 
       NEWFIELD = CASE WHEN LOCATION = 
                  (SELECT TOP 1 t2.Location 
                   FROM dbo.TableName t2
                   ORDER BY t2.ItemKey DESC)
                  THEN 'T' ELSE 'F' END
FROM  dbo.TableName t1

Upvotes: 1

Related Questions