Combine duplicate rows to output a single row

Question

I have a table for a ticket system that has the following cols;

order<string> startDate<datetime> endDate<datetime>

The majority of rows are not duplicated based on a column 'order' however in the scenario where an 'order' crosses from one day to the next it is split into 2 orders, the 1st has an endDate of 5pm (end of day) and the 2nd has a startDate of 8am (start of day). Their corresponding start and end dates are as needed. Some orders can be >2 days long and so will be split into >2 rows.

example

order startDate endDate
1 2016-03-29 11:00:53.000 2016-03-29 17:00:53.000
1 2016-03-30 08:00:53.000 2016-03-30 12:48:53.000
2 2016-03-30 10:17:53.000 2016-03-30 13:08:53.000

would transform to 
1 2016-03-29 11:00:53.000 2016-03-30 12:48:53.000
2 2016-03-30 10:17:53.000 2016-03-30 13:08:53.000

I need to combine all rows to give me a table of unique 'order' ids with start and ends. i.e. a row with the lowest start date of its duplicates and highest enddate of its duplicates.

I plan to do this by creating a new table and populating it and can choose 1 of duplicate rows based on a certain value but in not sure how to create a new row based on values from multiple rows.

VoteyDisciple VoteyDisciple · Accepted Answer · 2016-03-23T11:49:05

SELECT order, MIN(startDate), MAX(endDate)
FROM your_table_name
GROUP BY order

There may be no need to create a new table for this — GROUP BY queries are extremely common in production usage, and there's no inherent harm in simply running that query to get the results you need when you need them.

Combine duplicate rows to output a single row

1 Answers