Sorting a table

Question

I want to sort the below table XYZ using sql,
here Id is the primary key.

----------------------------------------
Id | Date        |Product   | Time 
----------------------------------------
1  | 01-02-2016  |C         |10:00
2  | 01-02-2016  |B         |10:30
3  | 01-02-2016  |A         |11:00
4  | 01-02-2016  |C         |11:30
5  | 01-02-2016  |B         |12:00
6  | 01-02-2016  |A         |12:30
7  | 01-02-2016  |C         |13:00
8  | 01-02-2016  |B         |13:30
9  | 02-02-2016  |D         |07:00
10 | 02-02-2016  |C         |07:30
11 | 02-02-2016  |E         |08:00
12 | 02-02-2016  |D         |08:30
13 | 02-02-2016  |E         |09:00
14 | 02-02-2016  |A         |09:30

I need to sort it in such a way to find the Product with the minimum time for each day and sort by time, then the Product with the next earliest time and sort by time in the following manner.

Id  |Date      |Product |Time 
1   |01-02-2016|    C   |10:00
4   |01-02-2016|    C   |11:30
7   |01-02-2016|    C   |13:00
2   |01-02-2016|    B   |10:30
5   |01-02-2016|    B   |12:00
8   |01-02-2016|    B   |13:30
3   |01-02-2016|    A   |11:00
6   |01-02-2016|    A   |12:30
9   |02-02-2016|    D   |07:00
12  |02-02-2016|    D   |08:30
10  |02-02-2016|    C   |07:30
11  |02-02-2016|    E   |08:00
13  |02-02-2016|    E   |09:00
14  |02-02-2016|    A   |09:30

Paul Spiegel · Accepted Answer · 2016-02-20 20:41:44Z

3

select t1.*
from XYZ t1
join (
  select Date, Product, min(Time) as minTime
  from XYZ 
  group by Date, Product
) t2 using(Date, Product)
order by str_to_date(t1.Date, '%d-%m-%Y'), t2.minTime, t1.Time

http://sqlfiddle.com/#!9/00209f/3

edited Feb 20, 2016 at 20:41

answered Feb 20, 2016 at 19:47

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Paul Spiegel Over a year ago

@Hogan I don't get your point. I get the min time for each product and date in the subquery. And then i'm sorting by minTime for each day.

PerlDuck Over a year ago

@Hogan I'm unable to see the fiddle (I think the server is overloaded) but I created a table in my local DB. Pauls statement produces exactly the desired output. +1 for this.

Hogan Over a year ago

Ok this is very subtle so it should explain more -- when you order by t2.minTime you are actually partitioning by Date and Product since the sub-query is grouped by those two fields. Nice job.

PerlDuck Over a year ago

@Hogan Ahh, now it works. Slooooowly. Probably they are throttling people without an "a" in their nicknames. ;-)

Hogan Over a year ago

@PerlDog -- That makes sense 8)

|

alambrache · Accepted Answer · 2016-02-20 19:43:37Z

-1

If i understand correctly what u want to do is the following:

Select * from XYZ
order by date ,time

This will order the days, and then the time within each day.

answered Feb 20, 2016 at 19:43

alambrache

1081 silver badge9 bronze badges

2 Comments

Hogan Over a year ago

This is not what the question asked for.

Hogan Over a year ago

This won't work you need to have the product with the min time ordered for each day.

Collectives™ on Stack Overflow

Sorting a table

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related