select data group by distinct date and get first and last data in the group

Question

I am not sure if this is doable, i have a table of data with timestamp

e.g.

apple    2013-01-02 06:24:22
banana    2013-01-12 11:08:22
apple    2013-01-02 15:24:22
apple    2013-01-02 12:30:16
banana    2013-01-14 18:34:22

i want to:

rearrange the data base on distinct date not timetamp, means all same date will group together

if possible i hope to achieve the below in final result

apple     2013-01-02 06:24:22  2013-01-02 15:24:22  // get first and last data
banana    2013-01-12 11:08:22  2013-01-12 11:08:22  // only one data in the group
banana    2013-01-14 18:34:22  2013-01-14 18:34:22  // only one data in the group

How can i do this with minimum sql queries instead of using a loop to determine?

score 0 · Accepted Answer · answered Dec 19 '14 at 06:37

0

Try this:

SELECT fruitName, MIN(a.timestamp), MAX(a.timestamp)
FROM tableA a 
GROUP BY DATE(a.timestamp), fruitName;

answered Dec 19 '14 at 06:37

Saharsh Shah

28,687
8
48
83

hi, any solution for part two? some groups may have 5-6 records, some may only have one – onegun Dec 19 '14 at 06:41

score 0 · Answer 2 · answered Dec 19 '14 at 06:39

0

Use MySQL DATE() function.

SELECT FRUIT_NAME, DATE(mydate) AS dateStamp FROM TABLE 
GROUP BY DATE(TABLE.mydate);

Where:

FRUIT_NAME: your field for name

mydate your date field.

answered Dec 19 '14 at 06:39

Pupil

23,834
6
44
66

thanks, actually i am more concern on part two question, do you have any solution? – onegun Dec 19 '14 at 06:42

score 0 · Answer 3 · answered Dec 19 '14 at 06:49

This query should get you what you need. (Question's 2nd Part)

SELECT Fruit, Min(DateTime) AS FirstDateTime, Max(DateTime) AS LastDateTime FROM Table1 
GROUP BY DATE(DateTime);

This would give you the output

FRUIT      FIRSTDATETIME           LASTDATETTIME
apple     2013-01-02 06:24:22  2013-01-02 15:24:22
banana    2013-01-12 11:08:22  2013-01-12 11:08:22 
banana    2013-01-14 18:34:22  2013-01-14 18:34:22

select data group by distinct date and get first and last data in the group

3 Answers3