CASE statement with WHEN EXISTS

• SQLPain

  Hall of Fame

  Points: 3522

  More actions

  February 15, 2016 at 5:11 pm

  #308051

  Trying to write a case statement with EXISTS.

  RULE: use name when prim = 0 is available for that id, if not then use the name when prim = 1. there would always be a name with prim = 1 for each ID. but there might not be a row (name) with prim = 0 all the time.

  For example: for ID 1, it should be John, for ID 2, it should be Mike, for ID 3 it should be TOM, and for ID 4 it should be Chris

  ID .................NAME....................Prim

  1...................JOHN.....................0

  1...................CENA.....................1

  2....................Mike......................0

  2....................Jack.......................1

  3....................TOM.......................1

  4....................Chris.......................1

  Thanks

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  February 15, 2016 at 5:20 pm

  #1858879

  Is the following an accurate rewrite of your requirement?

  "For each ID, select the row with the lowest prim value"

  If so, you're looking at a much simpler query, like:

  WITH Names AS (

  SELECT ID, NAME, Prim, RANK() OVER(PARTITION BY ID, ORDER BY Prim) AS RowRank

  FROM <your tables>

  WHERE <your filters>

  )

  SELECT ID, NAME, Prim

  FROM Names

  WHERE RowRank = 1

  ORDER BY <your ordering>

  Eddie Wuerch
  MCM: SQL