90abyss 90abyss - 2 months ago 6
SQL Question

SQL: Fetch rows with least date + Use CASE WHEN with select statement

I have a table with 3 columns (id, type, date) with following values:

id type date
--- --- ---
191 0 2016-09-15 11:26:51.000
191 1 2016-09-15 11:30:31.000
200 0 2016-09-15 17:36:19.000
200 1 2016-09-15 18:26:51.000
331 0 2016-09-16 07:26:22.000


This is what I want to do:

For each ticketid above, select the row with least minimum date. Once that is done I want another column in my output called "isEngaged" which will be set to 1 if the ticketid has a type = 1 present for any of its records.

My desired output:

id type date isEngaged
--- --- --- ---
191 0 2016-09-15 11:26:51.000 1
200 0 2016-09-15 17:36:19.000 1
331 0 2016-09-16 07:26:22.000 0


This is what I have so far: (this takes care of returning only the row with least date)

SELECT id, type, date FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date ASC) AS [Row], id, type, date)
FROM mytable WHERE type IN (0)) WHERE [Row] = 1


Really not sure how to incorporate the isEngaged part. I tried to use CASE WHEN but not sure how to go forward with this:

SELECT id, type, date,
"isEngaged" = CASE WHEN (SELECT * FROM mytable where type in (1) and id = <not sure what to put here>) THEN 1 ELSE 0 END
FROM mytable


If you know of a better way to do this then please let me know.

Answer

If you only accept 0/1 values in column type then you could use MAX() as a window function.

SELECT  
  id, type, date, isEngaged
FROM (
  SELECT
    id, type, date,
    MAX(type) OVER (PARTITION BY id) AS isEngaged
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
  FROM mytable
  ) t
WHERE rn = 1

If you accept values higher than 1, then appropriate CASE statement within the MAX() would suffice:

SELECT  
  id, type, date, isEngaged
FROM (
  SELECT
    id, type, date,
    MAX(CASE WHEN type = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY id) AS isEngaged
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) AS rn
  FROM mytable
  ) t
WHERE rn = 1