1

I have a table like this:

ID      Severity    WorkItemSK
23636   3-Minor     695119
23636   3-Minor     697309
23647   2-Major     695081
23647   2-Major     694967

In here I have several WorkItems that share the same ID. How can I get unique IDs that have the highest WorkItem?

So it would like this:

ID      Severity    WorkItemSK
23636   3-Minor     697309
23647   2-Major     695081

Help the noob :) Mind giving a clue what SQL commands (again I am a noob) should I use? Or an example of a query?

Thank you in advance!

mc110
  • 2,825
  • 5
  • 20
  • 21
GrimSmiler
  • 561
  • 1
  • 4
  • 21
  • possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – FuzzyTree Jun 21 '14 at 23:21

2 Answers2

2

Assuming that Severity can change depending on the WorkItemSK, you'll want to use the following query:

Select  T.ID, T.Severity, T.WorkItemSK
From    Table T
Join
(
    Select  ID, Max(WorkItemSK) As WorkItemSK
    From    Table 
    Group By ID
) D On T.WorkItemSK = D.WorkItemSK And T.ID = D.ID

The last Join condition of T.ID = D.ID may or may not be needed, depending on whether WorkItemSK can appear multiple times in your table.

Otherwise, you can just use this:

Select  ID, Severity, Max(WorkItemSK) As WorkItemSK
From    Table 
Group by ID, Severity

But if you have different Severity values per ID, you'll see duplicate IDs.

Siyual
  • 16,415
  • 8
  • 44
  • 58
0

Use select with GROUP BY: SELECT id,MAX(WorkItemSK) FROM table GROUP BY id;

CaptainCap
  • 334
  • 2
  • 13