1

I am trying to pick the rows based on the oldest timestamp per name. I referred this solution but the problem is my table doesn't have id column. I tried to add an temp auto increment id column but was not sure how to do it. basically when the timestamp is same I want to pick any one of the available records for the given name. Can someone please help me out here.

mysql> select * from tblemployee order by name;
+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| bob  | wednesday   | 2017-08-11 08:11:30 |
| bob  | thursday    | 2017-06-11 11:21:30 | << same timestamp
| bob  | saturday    | 2017-08-28 09:01:30 |
| bob  | wednesday   | 2017-08-11 08:11:30 |
| bob  | monday      | 2017-06-11 11:21:30 | << same timestamp
| tom  | wednesday   | 2017-08-28 23:01:20 |
| tom  | sunday      | 2017-08-29 09:01:30 |
+------+-------------+---------------------+

My expected result would be something like this

For 'tom'

+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| tom  | wednesday   | 2017-08-28 23:01:20 |

For 'bob' since there are two records with same timestamp , I can pick any one irrespective of day_of_week.

+------+-------------+---------------------+
| name | day_of_week | signin_date         |
+------+-------------+---------------------+
| bob  | thursday    | 2017-06-11 11:21:30 |

                    (or)

| bob  | monday      | 2017-06-11 11:21:30 |
chidori
  • 1,052
  • 3
  • 12
  • 25
  • Do you want just one person (bob) or a set of people? for 1, ORDER BY and LIMIT 1. I'm guessing from "rows" you want both bob and tom, but it isn't 100% clear to me. – Dave S Aug 30 '17 at 18:25
  • I would like to pick one record for each user based on oldest timestamp. – chidori Aug 30 '17 at 18:27

1 Answers1

1

You can use a join on the group value for min(signin_date) eg: 

  select distinct a.name, a.day_of_week , a.signin_date         
  from tblemployee a
  inner join (
    select name, min(signin_date) as min_date
    from tblemployee
    group by name
  ) t on t.min_date = a.signin_date and a.name = t.name

if you have ambiguos rows as for bob the min(signin_date) i related to two different day then you should get one of this using

  select a.name, min(a.day_of_week) , a.signin_date         
  from tblemployee a
  inner join (
    select name, min(signin_date) as min_date
    from tblemployee
    group by name
  ) t on t.min_date = a.signin_date and a.name = t.name 
  group by a.name,a.signin_date
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Sorry, that doesn't seem to work for me. The columns get duplicated and i see record related to bob appearing twice. – chidori Aug 30 '17 at 18:34
  • Thanks, the column names look good now but I still see the query returning two records in case of name 'bob'. – chidori Aug 30 '17 at 18:43
  • this because Bob has 2 lines with the same value ( bob | wednesday | 2017-08-11 08:11:30).. is it a mistake or is it a situation to handle ?? – ScaisEdge Aug 30 '17 at 18:46
  • actually the two records of bob are ( bob | monday | 2017-06-11 11:21:30 ) and ( bob | thursday | 2017-06-11 11:21:30) , in this case i need to pick any one of those. – chidori Aug 30 '17 at 18:49
  • i don't understand .. as result you want a single row for each name or not?and please update you data sample so i can evaluate the right one .. i can't imagine what you are doing .. – ScaisEdge Aug 30 '17 at 18:52
  • anyway i have update the answer adding a new suggestion for ambigous min date – ScaisEdge Aug 30 '17 at 18:56
  • Thanks, I have updated my original post with details. And now I see your newly updated query seem to meet my requirement. – chidori Aug 30 '17 at 18:59