Selecting unique rows on basis of certain criteria

I have an SQL table called "trainings" that looks like this:

+-----+-----------+--------+------------+------------+------------+-------+
| Id  | Booked    |Coach_No| Student_No | StartDate  | EndDate    | Color |
+-----+-----------+--------+------------+------------+------------+-------+
| 1   |     1     |   20   |   NULL     | 2011-03-18 |2011-03-19  |    3  |
| 2   |     1     |   20   |    45      |开发者_如何学C 2011-03-18 |2011-03-19  |    1  |
| 3   |     1     |   15   |    41      | 2011-03-20 |2011-03-21  |   18  |
| 4   |     0     |   21   |   NULL     | 2011-03-22 |2011-03-23  |    3  |
| 5   |     0     |   33   |   NULL     | 2011-03-20 |2011-03-21  |    3  |
| 6   |     0     |   34   |   NULL     | 2011-03-20 |2011-03-21  |    3  |
+-----+-----------+--------+------------+------------+------------+-------+

I'm looking to frame an SQL query that will fetch all the rows with unique start and end dates. For rows with duplicate start and end dates, I need to select those with a color of 1 or 18 in preference over those with a color of 3.

I've attempted to use the query below, but the distinct row that is selected is the one with the lowest Id

SELECT * FROM trainings GROUP BY StartDate,EndDate

What is the right approach?

You could group by on StartDate, EndDate, and select two ID's for the different color priorities. Then join back to the original table, preferring the high priority:

select  b1.*
from    Trainings b1
join    (
        select  max(case when Color in (1,18) then Id end) as HighPrioID
        ,       max(case when Color not in (1,18) then Id end) as LowPrioID
        from    Trainings
        group by
                StartDate
        ,       EndDate
        ) b2
on      b1.Id = COALESCE(b2.HighPrioID, b2.LowPrioID);

Test data:

drop table if exists Trainings;
create table Trainings (id int, StartDate datetime, EndDate datetime, Color int);

insert Trainings values
(1,'2011-03-18','2011-03-19', 3),
(2,'2011-03-18','2011-03-19', 1),
(3,'2011-03-20','2011-03-21',18),
(4,'2011-03-22','2011-03-23', 3),
(5,'2011-03-20','2011-03-21', 3);

SELECT DISTINCT CONCAT(StartDate, EndDate) FROM trainings

If I understood it right.

Is it your mean

SELECT * FROM trainings WHERE color IN (1,18) GROUP BY StartDate,EndDate

Assuming StartDate, EndDate and Colors results in unique records ....

SELECT * FROM
From Trainings T
(
  SELECT
   StartDate,
   EndDate,
   MAX(CASE WHEN Color = 3 THEN 0 ELSE Color END) Color
  From Trainings
  GROUP By StartDate, EndDate
) T1 on T.StartDate = T1.StartDate AND T.EndDate = T1.EndDate AND T.Color = T1.Color

You can do something like this :

select 
    t1.*,
    case
        when t2.Id is null then 1
        when t1.color in (1,18) then 2
        else 3
    end as priority
from trainings as t1
left join trainings as t2 on
    t1.StartDate = t2.StartDate and
    t1.EndDate = t2.EndDate and
    t1.Id != t2.Id
order by priority

The value of priority will help you find what you want :

• rows with priority 1 have unique start and end date
• rows with priority 2 have 1 or 18 has color
• all other rows have priority 3