SQL - If two rows have the same date, only show row with certain column value

Question

Is there a way to show only one of two rows that have the same date and id, by using another column that is a nvarchar.

IF I HAD:

groupID     Date         Task
1           1-01-1111    First Task
2           1-02-1122    First Task
2           1-02-1122    Second Task
3           1-03-1133    Second Task
3           1-03-1133    Third Task

I Would like to get:

groupID     Date         Task
1           1-01-1111    First Task
2           1-02-1122    First Task
3           1-03-1133    Second Task
3           1-03-1133    Third Task

If rows have the same groupID and Date AND if one of the Task in the rows are "First Task" only show the row with the Task of "First Task".

If rows have the same groupID and Date, but no row has a Task of "First Task", show all rows.

Also, how would you decide which task is the one to keep in the group of two? — Joachim Isaksson, Feb 03 '16 at 21:20
What should happen if there's two rows with the same date, and neither of them has "First Task" for the Task column? — Tab Alleman, Feb 03 '16 at 21:28
@AXMIM and Joachim Isaksson. For the sake of the query "First Task" is the only relevant thing. If rows have the same groupID and Date, if one of those records have a task of "First Task" That record should be the only one of them to show. — Demetrick Norris, Feb 04 '16 at 03:57
@Tab Allerman - If neither has a Task of "First Task", then show both of them. — Demetrick Norris, Feb 04 '16 at 04:00
You may need to add a column with identity for each insertion. — Ullas, Feb 04 '16 at 13:47

score 1 · Answer 1 · edited May 23 '17 at 10:28

1

You can use row_number function to set task position per group and then just keep record with position set to 1. So in the following query, a task named "First task" will have the first position. If there is no such task, then you could let the SQL engine choose which one will be first, but this is a bad practice. So for this sake, here I specified alphabetic order on Task.

So this mean, 'First task' is first, otherwise 'another task' is.

SELECT  groupID,
        [Date],
        Task
FROM    (SELECT groupID,
                [Date],
                Task,
                i = ROW_NUMBER() OVER(PARTITION BY groupID, [Date] ORDER BY CASE WHEN UPPER(Task) = 'FIRST TASK' THEN 0 ELSE 1 END, Task)
        FROM @YourTableName) AS t
WHERE i = 1 --Only show the first row for a group with same groupID and Date

N.B : UPPER is used to handle possible data irregularity, like 'First Task', 'first task'.

edited May 23 '17 at 10:28

Community

1
1

answered Feb 03 '16 at 21:25

AXMIM

2,424
1
20
38

Using Alphabetic order, "Eighth Task" would come before "First Task". – Tab Alleman Feb 03 '16 at 21:34
This wouldn't work in my situation because the Tasks are nvarchar, so they can't be ordered by number. – Demetrick Norris Feb 04 '16 at 13:34
@demetrick sorry, I taught "First task" was just an example to simplify your problem. I updated my answer and also handled the "no first task" case. – AXMIM Feb 04 '16 at 14:01
@AXMIM Its all good. I did simplify the information, but I made sure the data shown reflected the types used. Sorry about the confusion. – Demetrick Norris Feb 04 '16 at 14:31

score 1 · Answer 2 · answered Feb 03 '16 at 21:27

1

You can use a column Row_number with Partition where this column is 1.

select * from (
    Select groupID, Date, Task
    , row_number () over (Partition by goupId, Date, order by groupId, Date, Task) as line
) as t1
where
     t1.line = 1

answered Feb 03 '16 at 21:27

Thiago

56
4

This almost worked, but needed a way to order the Task since the database doesn't do it by number. – Demetrick Norris Feb 04 '16 at 14:08

score 1 · Accepted Answer · answered Feb 03 '16 at 21:32

1

Given only your sample data, and stated requirement, I would do it like this:

WITH cte AS (
  SELECT *,
    ROW_NUMBER() OVER (Partition By GroupId, [Date] ORDER BY CASE
      WHEN Task = 'First Task' THEN 0
      ELSE 1
    END ASC)
  FROM MyTable
)
SELECT GroupId, [Date], Task FROM cte

If a given GroupId and Date has multiple rows, and none of them have "First Task" for the task, then one will be selected randomly.

answered Feb 03 '16 at 21:32

Tab Alleman

31,483
7
36
52

Everyone one was on the right track, but the use of CASE in the ORDER BY part of the Partition is what I really needed to order the task in the desired way to get the results needed. I ended up taking the select and putting it in a LEFT JOIN. – Demetrick Norris Feb 04 '16 at 14:06

SQL - If two rows have the same date, only show row with certain column value

3 Answers3