Codeigniter - How to select row id's of matching date column in query

Question

I am trying to write a query that outputs the shiftId's into an array. I have a table that looks like this.

+---------+----------+-------+
| shiftId | endTime  | shift |
+---------+----------+-------+
|       1 | 03/03/19 |     1 |
|       2 | 03/03/19 |     2 |
|       3 | 03/01/19 |     1 |
|       4 | 03/01/19 |     2 |
+---------+----------+-------+

I want to return the shiftId of each date with the largest shift, and not sure how to go about.

I want my array to look like below, based on above table.

Array
(
    [0] => 2
    [1] => 4
)

I have tried to group_by date and then select_max of each shift but don't think I'm on the correct path. Any help would be appreciated.

I want to select shiftId foreach date where shift # is the largest.

Marleen · Accepted Answer · 2019-03-04 11:35:21Z

2

You were on the right path!

Either use (this shows the SQL more clearly):

$query = $this->db->query('SELECT max(shiftId) shiftId FROM yourtable GROUP BY endTime')->result_array();

Or (if you want to use CI's query builder):

$query = $this->db->select_max('shiftId')->group_by('endTime')->get('yourtable')->result_array();

Both of these group the table by endTime, and then return the maximum shiftId for each group of identical endTimes. Both give an array that looks like this:

Array
(
    [0] => Array
        (
            [shiftId] => 2
        )

    [1] => Array
        (
            [shiftId] => 4
        )

)

To get rid of the shiftId index in the result and get the exact array structure from your OP, use:

array_column($query, 'shiftId');

Edit

If you want to get the shiftId for each endTime + MAX(shift) combination, use this:

SELECT shiftId FROM yourtable
WHERE CONCAT(endTime, "-", shift) IN (
    SELECT CONCAT(endTime, "-", MAX(shift)) FROM yourtable GROUP BY endTime
)

The inner query (after IN) does more or less the same as the previous query: it groups the records in the table by endTime, then gets the maximum shift for each group of identical endTimes, and here it concatenates this with the endTime and a dash.

You need to concatenate endTime with MAX(shift) here, because MAX(shift) alone is not unique in the table (there's more than one shift with number 2, for example), and neither is endTime.

The outer query (SELECT shiftId...) then finds the matching shiftId for each endTime + MAX(shift) combination and returns that.

You need to use two (nested) queries for this, because the inner one uses grouping and the outer one doesn't, and you're not allowed to mix those two types in one query.

Note: CONCAT only works in MySQL, if you're using a different database type, you might have to look up what concatenation syntax it uses (could be + or || for example).

In CI:

$query = $this->db->query('SELECT shiftId FROM yourtable
WHERE CONCAT(endTime, "-", shift) IN (SELECT CONCAT(endTime, "-", MAX(shift)) FROM yourtable GROUP BY endTime)')->result_array();

edited Mar 4, 2019 at 11:35

answered Mar 3, 2019 at 21:28

Marleen

2,9162 gold badges17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

XxnumbxX Over a year ago

I think I spoke to soon. This worked in this scenario but doesn't work shifts are in different order. I am actually trying to get the shiftId where shift is max. $this->db->select('shiftId, MAX(shift)')->group_by('DATE(endTime)')->get('richShift')->result_array(); but this returns an error

Marleen Over a year ago

Updated my post with a new query, hopefully this'll do what you need. :)

XxnumbxX Over a year ago

This does work but, I how do I format the date in sql? I tried formatting as DATE(endTime) but it returns a db error. Here is my statement. $query = $this->db->query('SELECT shiftId FROM richShift WHERE CONCAT(DATE(endTime), "-", shift) IN (SELECT CONCAT(DATE(endTime), "-", MAX(shift)) FROM richShift GROUP BY DATE(endTime))')->result_array();

Marleen Over a year ago

What format does your endTime column use? Your query (with the added DATE()) works for me, when using either DATE type or DATETIME type for the endTime column.

XxnumbxX Over a year ago

My column is a DATETIME. Here is the error "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'intranet.richShift.endTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SELECT shiftId FROM richShift WHERE CONCAT(DATE(endTime), "-", shift) IN (SELECT CONCAT(DATE(endTime), "-", MAX(shift)) FROM richShift GROUP BY DATE(endTime))

|

Collectives™ on Stack Overflow

Codeigniter - How to select row id's of matching date column in query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related