CODEWITHSUNDEEP
phpmysqlactiverecordcodeigniter-3
XxnumbxX
XxnumbxX

Reputation: 134

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

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.

Upvotes: 0

Views: 1279

Answers (1)

Marleen
Marleen

Reputation: 2724

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();

Upvotes: 2

Related Questions