CODEWITHSUNDEEP
mysqlsql
Hendrik Eka
Hendrik Eka

Reputation: 145

SQL: How can I add a custom row before specific date

I have a query and the result is like below:

date      From
-----------------------
20        Airport
20        Vehicle
23        Person
24        Person
28        Person
28        Airport

What I expected:

date      From
-----------------------
20        Received From
20        Airport
20        Vehicle
23        Received From
23        Person
24        Received From
24        Person
28        Received From
28        Person
28        Airport

How do I add the "Received From" rows automatically?

Upvotes: 0

Views: 73

Answers (1)

Ullas
Ullas

Reputation: 11556

First add Received From for all the each date column value and combine the actual table rows with this using UNION ALL. Then order the result using a CASE expression to come Received From value first.

Query

select t.`date`, t.`From` from(
  select `date`, `From` from `myTable`
  union all
  select distinct `date`, 'Received From' from `myTable`
) t
order by t.`date`,
case t.`From` when 'Received From' then 1 else 2 end, t.`From`;

Upvotes: 1

Related Questions