Breaking News: Grepper is joining You.com. Read the official announcement!

mysql get complete row from group

Luciano I. answered on December 19, 2022 Popularity 3/10 Helpfulness 1/10

answer mysql get complete row from group

mysql get complete row from group

Comment

//subquery where the grouping and filtering happens
$subquery = (new Query())
            ->select([
                'persona_id', 'MAX(created_at) AS max_created_at'
            ])
            ->from('persona_saldo_reale')
            ->where([
                'persona_id' => $id
            ])
            ->andWhere(['>=', 'DATE(created_at)', $from_date])
            ->andWhere(['<=', 'DATE(created_at)', $to_date])
            ->groupBy(['persona_id', 'DATE(created_at)']);       

        // ============================================================
//query to retrive from the main table the extra columns not included in groupBy
$array = (new Query())
            ->select([
                'DATE_FORMAT(persona_saldo_reale.created_at, "%d/%m/%Y %H:%i:%s") AS created_at',
                '(persona_saldo_reale.saldo_reale/100) AS saldo_reale',
            ])
            ->from('persona_saldo_reale')
			//join with subquery on all columns to make sure the join it's unique
            ->leftJoin(['subquery' => $subquery], 'subquery.persona_id = persona_saldo_reale.persona_id AND subquery.max_created_at = persona_saldo_reale.created_at')
            ->where([
  				//here make sure only the rows from the grouping subquery are selected 
                'not', ['subquery.persona_id' => null]
            ])
            ->orderBy(['persona_saldo_reale.created_at' => SORT_DESC])
            ->all();

Popularity 3/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: get group mysql row sql

Link to this answer
Share Copy Link

Contributed on Dec 19 2022

Luciano I.

0 Answers Avg Quality 2/10

mysql get complete row from group

Contents

More Related Answers

mysql get complete row from group

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.