Get records for each person's each day's min datetime

Question

CREATE TABLE IF NOT EXISTS `accesscards` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `department` varchar(255) NOT NULL,
    `name` varchar(255) NOT NULL,
    `entrydates` datetime NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `accesscards` (`id`, `department`, `name`, `entrydates`) VALUES
(1, 'test', 't1', '2013-12-06 16:10:00'),
(2, 'test', 't1', '2013-12-06 15:10:00'),
(3, 'test', 't1', '2013-12-07 15:11:00'),
(4, 'test', 't1', '2013-12-07 15:24:00'),
(5, 'test', 't2', '2013-12-06 16:10:00'),
(6, 'test', 't2', '2013-12-06 16:25:00'),
(7, 'test', 't2', '2013-12-07 15:59:00'),
(8, 'test', 't2', '2013-12-07 16:59:00');

Above is my query, I want to get records for a person for each day. And that record should have min datetime for the day. I need whole record for that date time

My expected output here

I tried using

SELECT id, MIN(entrydates) FROM accesscards WHERE 1=1 AND name!='' GROUP BY DATE(entrydates) ORDER BY id

but for 't1' I got id=1 and entrydates of first row.

Please help me out. If duplicate then provide link.

How about you search SO? There are hundreds of similar questions. — Barmar, Jan 18 '14 at 06:25
Have you looked at the similar questions? Don't they help you? — Barmar, Jan 18 '14 at 06:30
@Barmar I looked them but then could not retrieved same record for which I found MIN datetime — Parixit, Jan 18 '14 at 06:30
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barmar, Jan 18 '14 at 06:36
You just edited the question, but didn't change anything except the title. — Barmar, Jan 18 '14 at 07:04

Barmar · Accepted Answer · 2014-01-18T07:06:24.693

2

SELECT a1.*
FROM accesscards a1
JOIN (SELECT name, MIN(entrydates) mindate
      FROM accesscards
      WHERE name != ''
      GROUP BY name, date(entrydates)) a2
ON a1.name = a2.name AND a1.entrydates = a2.mindate

DEMO

edited Jan 18 '14 at 07:06

answered Jan 18 '14 at 06:39

Barmar

741,623
53
500
612

Fixed it, see the demo now. – Barmar Jan 18 '14 at 06:45
This is not what I want. Check [here](http://www.sqlfiddle.com/#!2/66c3f/2). This is something I need. – Parixit Jan 18 '14 at 06:58
You said you want the row with the min time for each day. Your output has two rows for each day. You need to update the question to describe the criteria better. Do you want the minimum row for each day, department, and name? – Barmar Jan 18 '14 at 07:01
can't you extrapolate from my answer to what you want? If you can't, you're never going to make it in this business -- you can't really expect randoms on the Internet to do your work for you every day, you have to learn to code for yourself. – Barmar Jan 18 '14 at 07:02
Thanks for your time. Its working for my need just changing `group by clause's order of columns` Hope this question might not duplicate of anyone!! – Parixit Jan 18 '14 at 07:12
The order of columns in `GROUP BY` shouldn't make a difference. – Barmar Jan 18 '14 at 07:18
Hope so, but it fulfil my need. – Parixit Jan 18 '14 at 07:19
My query has the same result as your expected output, why do you think you need to change the order of columns in GROUP BY? – Barmar Jan 18 '14 at 07:21
1

If you need to order the final results differently, use `ORDER BY`. – Barmar Jan 18 '14 at 07:21
My mistake, only I should have to order by id – Parixit Jan 18 '14 at 07:22

aconrad · Answer 2 · 2014-01-18T07:14:12.563

If you are using mysql : GROUP_CONCAT and SUBSTRING_INDEX

SELECT 
  DATE(entrydates) AS grouped_date,
  GROUP_CONCAT(id ORDER BY entrydates ASC SEPARATOR ',') AS id_ordered_list,
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY entrydates ASC), ',', 1) AS min_id_for_day
FROM 
  accesscards 
WHERE 
  1=1 AND name!='' 
GROUP BY 
  DATE(entrydates)

If you need other fields besides id to be shown, add this to you select :

  SUBSTRING_INDEX(GROUP_CONCAT(YOUR_FIELDNAME_HERE ORDER BY entrydates ASC), ',', 1) AS min_YOUR_FIELDNAME_for_day

Play at http://sqlfiddle.com/#!2/a2671/13

After you updated your question with new data: http://sqlfiddle.com/#!2/a2671/20

SELECT 
  DATE(entrydates) AS grouped_date,
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY entrydates ASC), ',', 1) AS min_id_for_day,
  department,
  name,
  SUBSTRING_INDEX(GROUP_CONCAT(entrydates ORDER BY entrydates ASC), ',', 1) AS min_entrydate_for_day
FROM 
  accesscards 
WHERE 
  1=1 AND name!='' 
GROUP BY 
  name,DATE(entrydates) 
ORDER BY entrydates

score 0 · Answer 3 · answered Jan 18 '14 at 07:02

0

Try this out this will surely help you

select id,department,name,entrydates from accesscards where entrydates in (select min(entrydates) from accesscards group by to_char(entrydates,'dd-Mon-yyyy')) order by id;

answered Jan 18 '14 at 07:02

smn_onrocks

1,282
1
18
33

Get records for each person's each day's min datetime

3 Answers3

Linked