CODEWITHSUNDEEP
mysqlsql
Naweed Chougle
Naweed Chougle

Reputation: 520

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      | 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?

Upvotes: 1

Views: 976

Answers (5)

Andomar
Andomar

Reputation: 238296

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);

Upvotes: 2

krtek
krtek

Reputation: 26617

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

Upvotes: 0

Nitin Midha
Nitin Midha

Reputation: 2268

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

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86476

Is it your mean

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

Upvotes: 0

Thinker
Thinker

Reputation: 14474

SELECT DISTINCT CONCAT(StartDate, EndDate) FROM trainings

If I understood it right.

Upvotes: 0

Related Questions