0

I am trying to select rows in an SQL table based on multiple conditions within the same unique ID.

I have the following table:

ID       Status
1         AS
2         Rec
2         AS

The rules are as follows:

  • If an [ID] has both 'Rec' and 'AS', select only the row with 'Rec'. In other words, 'Rec' has precedence over 'AS'.

  • If the [ID] does not have 'Rec', select the row with 'AS'.

I want the query to output only the rows ID=1,Status=AS and ID=2,Status=Rec.

What is the query to select only these two rows?

BansheeAce
  • 1
  • 2

3 Answers3

1

Can you create a helper column or field that serves as an order for your values?

Rec = 3
AS = 2
....

Then, see the description and related article here: https://stackoverflow.com/a/2129703/5819421

The article talks of "Selecting the one maximum row from each group" which sounds like how this would work for you.

Community
  • 1
  • 1
OptimisticToaster
  • 19
  • 5
  • I cannot create additional columns. It would be easy if I could simply add a `WHERE [newCol] = 'whatever'` condition but I do not have control over this. – BansheeAce Apr 28 '16 at 17:57
1
; WITH CTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY status DESC) rn, ID, Status
    FROM (
        SELECT DISTINCT id, status 
        FROM MyTable
    )--in case you have several Rec's and AS's
)
SELECT id, status
FROM CTE
WHERE rn = 1
Ɖiamond ǤeezeƦ
  • 3,223
  • 3
  • 28
  • 40
Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36
  • Of course there may be a need to further filter the derived table in the CTE if there are also other status values that you don't want to consider. – HLGEM Apr 28 '16 at 21:51
0

Psuedo code:

SELECT DISTINCT Id, Status
FROM MyTable
WHERE Status = 'Rec'
OR (
 Status = 'AS'
 AND NOT EXISTS(
   SELECT * FROM MyTable WHERE Status='Rec', correlated to outer query on Id
 )
)

In English, get a distinct set of rows where Status is "Rec" or where status is "AS" and there's no "Rec" for the same Id.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52