How to select other columns other than group by in mysql

Question

I am actually having a database just like this

+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| org_entry_id | int(10) unsigned | NO   | MUL | NULL              |                             |
| check_in     | datetime         | YES  |     | NULL              |                             |
| check_out    | datetime         | YES  |     | NULL              |                             |
| created_at   | datetime         | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at   | datetime         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| deleted_at   | datetime         | YES  |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+

Now I want to get id field and max(check_in) from this table and that too groupBy org_entry_id

I have tried query like this but it will give full groupBy mode error

select id, max(check_in) from time_sheets group by org_entry_id;

So I have read the manual and I found that it was written to ANY_VALUE but that solution I don't want . I want the exact correct id which is in front of the current max(check_in).

This is some sample data

+----+--------------+---------------------+---------------------+---------------------+---------------------+------------+
| id | org_entry_id | check_in            | check_out           | created_at          | updated_at          | deleted_at |
+----+--------------+---------------------+---------------------+---------------------+---------------------+------------+
|  1 |            3 | 2018-04-03 01:48:07 | NULL                | 2018-04-03 13:53:29 | 2018-04-03 13:53:29 | NULL       |
|  2 |            3 | 2018-04-04 01:48:07 | 2018-04-04 01:48:07 | 2018-04-03 14:00:00 | 2018-04-03 15:23:52 | NULL       |
|  3 |            3 | 2018-04-04 01:48:07 | 2018-04-04 01:48:07 | 2018-04-03 14:00:30 | 2018-04-03 15:23:52 | NULL       |
|  4 |            3 | 2018-04-04 03:25:07 | NULL                | 2018-04-03 15:25:43 | 2018-04-03 15:25:43 | NULL       |
|  5 |            3 | 2018-04-05 01:25:07 | 2018-04-05 03:48:07 | 2018-04-03 15:26:01 | 2018-04-03 16:06:15 | NULL       |
|  6 |            3 | 2018-04-05 01:25:07 | NULL                | 2018-04-03 16:06:53 | 2018-04-03 16:06:53 | NULL       |
|  7 |            3 | 2018-04-05 03:25:07 | NULL                | 2018-04-03 16:07:22 | 2018-04-03 16:07:22 | NULL       |
+----+--------------+---------------------+---------------------+---------------------+---------------------+------------+

Now the situation is I want max(check_in) and id for org_entry_id 3 that is 7 ,2018-04-05 03:25:07 from the table.

Can there be any more possible solution for this .

This is a VERY common question. :) Here's one top answwer: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Vilx-, Apr 03 '18 at 10:42
@Vilx- I think in that group by is done by primary key but here I am doing group by non primary key — Vikas, Apr 03 '18 at 10:45
what is your previous output and what is your expecting output — Arunkumar Muthuvel, Apr 03 '18 at 10:46
@Vilx- No I want only the max value for particular org_entry_id and if I do group by id it will me all the rows . I want only one row with max time and its id — Vikas, Apr 03 '18 at 10:47
@Vikas - If there are multiple rows with the max `check_in` value for the same `org_entry_id` value, then which row do you want? — Vilx-, Apr 03 '18 at 10:50

Vilx- · Accepted Answer · 2018-04-03T11:01:04.460

If check_in values are unique within each org_entry_id, or you don't care that there might be multiple rows for each org_entry_id:

select
    b.*
from
    (select org_entry_id , max(check_in) check_in from time_sheets group by org_entry_id) a
    join time_sheets b on (a.org_entry_id=b.org_entry_id and a.check_in=b.check_in)

If check_in values are NOT unique within each org_entry_id, but you still want one row for each org_entry_id:

select
    d.*
from
    (select
        max(b.id) id
      from
        (select org_entry_id , max(check_in) check_in from time_sheets group by org_entry_id) a
        join time_sheets b on (a.org_entry_id=b.org_entry_id and a.check_in=b.check_in)
      group by 
        a.org_entry_id
    ) c
    join time_sheets d on c.id=d.id

If you're filtering by a specific org_entry_id while check_in do not have unique values, but you still want just one row in return:

select
    *
from
    time_sheets
where
    org_entry_id=123
order by
    check_in desc,
    id
limit 1

cdaiga · Answer 2 · 2018-04-03T13:57:00.153

0

Try this:

select id, check_in
from time_sheets a inner join
(select org_entry_id, max(check_in) check_in
from time_sheets group by org_entry_id) b
on a.check_in=b.check_in and a.org_entry_id=b.org_entry_id;

You are supposed to use an aggregate function on the all the attributes that are in the select clause but not in the group by clause.

edited Apr 03 '18 at 13:57

answered Apr 03 '18 at 10:48

cdaiga

4,861
3
22
42

The entry with the maximum `id` might not be the same as the entry with the maximum `check_id`. – Vilx- Apr 03 '18 at 10:50
@Vilx- thanks for the comment, I just updated my query. – cdaiga Apr 03 '18 at 13:58

score 0 · Answer 3 · answered Apr 03 '18 at 10:49

0

try this

select max(id),org_entry_id, max(check_in) from time_sheets group by org_entry_id;

answered Apr 03 '18 at 10:49

Arunkumar Muthuvel

423
3
18

The entry with the maximum `id` might not be the same as the entry with the maximum `check_id`. – Vilx- Apr 03 '18 at 10:49

How to select other columns other than group by in mysql

3 Answers3