CODEWITHSUNDEEP
sql-servergroup-bydistinct
Sanya
Sanya

Reputation: 1280

SQL Server : Select WHERE Field may not contain VALUE

I know the title is vague but I'm not sure how to phrase it.

I have a simple table (see image below).

I need to write a SQL statement that will retrieve a single record representing each [UNIT_ID] and [ISACTIVE] where [ISACTIVE] is the MAX for that particular [UNIT_ID].

enter image description here

Ideally, the result should look like this (notice that UNIT_ID=2 does not have ISACTIVE=1):

enter image description here

Doing a simple [ISACTIVE] > 0 IS NOT what I'm looking for because then UNIT_ID=2 would never show up in the record.

Here's what I've tried (failed):

SELECT 
    [ID], [UNIT_ID], [REV], [ISACTIVE], [DATE_ENTERED] 
FROM 
    TestTable 
WHERE
    ([ISACTIVE] = (SELECT MAX([ISACTIVE]) 
                   FROM TestTable 
                   WHERE ([UNIT_ID] = [UNIT_ID])));

-- This code only reveals two records (its missing record #4)

Any help on the matter is appreciated!

Upvotes: 0

Views: 60

Answers (3)

Marcos Echagüe
Marcos Echagüe

Reputation: 597

Like @forpas says, I think that the best option is this: 

SELECT t.[ID], t.[UNIT_ID], t.[REV], t.[ISACTIVE], t.[DATE_ENTERED] 
FROM (
  SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY [UNIT_ID] ORDER BY [UNIT_ID, ISACTIVE] DESC) AS row_number
  FROM TestTable
) AS t
WHERE t.row_number = 1

The only difference is the order by clause, that have to have those columns UNIT_ID, ISACTIVE.

Hope this can help you.

Upvotes: 1

Serg
Serg

Reputation: 22811

The problem with your code is the subquery doesn't use an alias for the TestTable. Should be

SELECT 
   [ID], [UNIT_ID], [REV], [ISACTIVE], [DATE_ENTERED] 
FROM TestTable ta
WHERE (
    [ISACTIVE]=
        (SELECT MAX(t.[ISACTIVE]) FROM TestTable t WHERE (t.[UNIT_ID]=ta.[UNIT_ID])));

Upvotes: 1

forpas
forpas

Reputation: 164099

With NOT EXISTS:

SELECT t.[ID], t.[UNIT_ID], t.[REV], t.[ISACTIVE], t.[DATE_ENTERED] 
FROM TestTable AS t
WHERE NOT EXISTS (
  SELECT 1 FROM TestTable
  WHERE [UNIT_ID] = t.[UNIT_ID] AND [ISACTIVE] > t.[ISACTIVE]
)

or with ROW_NUMBER():

SELECT t.[ID], t.[UNIT_ID], t.[REV], t.[ISACTIVE], t.[DATE_ENTERED] 
FROM (
  SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY [UNIT_ID] ORDER BY [ISACTIVE] DESC) AS rn
  FROM TestTable
) AS t
WHERE t.rn = 1

Upvotes: 2

Related Questions