CODEWITHSUNDEEP
mysqlsqlmaxaggregate
Sophia
Sophia

Reputation: 21

How to select rows with max values of 2 columns

I have a table mysql like this : 

CREATE TABLE prelevement (
  line int,
  facture varchar(30),
  date_op varchar(30),
  code_op varchar(30)
);

        insert into prelevement 
(line,facture,date_op,code_op)
values 
(1,'F1','2019-02-20','PREL'),
(2,'F1','2019-02-20','CART'),
(3,'F1','2019-02-20','REJE'),
(8,'F1','2019-02-19','PREL'),
(2,'F2','2019-02-15','PREL'),
(1,'F2','2017-01-25','PREL'),
(1,'F3','2018-02-25','REJ'),
(2,'F3','2018-02-25','CART');

For each facture, I am trying to select the row with the biggest 'line' from the latest 'date_op'. So from this : 

| line | facture | date_op    | code_op |
| ---- | ------- | ---------- | ------- |
| 1    | F1      | 2019-02-20 | PREL    |
| 2    | F1      | 2019-02-20 | CART    |
| 3    | F1      | 2019-02-20 | REJE    |
| 8    | F1      | 2019-02-19 | PREL    |
| 2    | F2      | 2019-02-15 | PREL    |
| 1    | F2      | 2017-01-25 | PREL    |
| 1    | F3      | 2018-02-25 | REJ     |
| 2    | F3      | 2018-02-25 | CART    |

I am trying to get this result: 

| line | facture | date_op    | code_op |
| ---- | ------- | ---------- | ------- |
| 3    | F1      | 2019-02-20 | REJE    |
| 2    | F2      | 2019-02-15 | PREL    |
| 2    | F3      | 2018-02-25 | CART    |

I've come to this but I am not sure where to go from here: 

select p.* 
        from prelevement p inner join(
            select facture, max(date_op) as 'date' 
            from prelevement    
            group by facture) p1 on p.facture=p1.facture and p.date_op=p1.date;

Upvotes: 2

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

In MySQL, you can use:

select p.*
from prelevement p
where (date_op, line) = (select date_op, line
                         from prelevement p2
                         where p2.facture = p.facture
                         order by date_op desc, line desc
                         limit 1
                        );

You can also do this with two correlated subqueries:

select p.*
from prelevement p
where p.date_op = (select max(p2.date_op)
                   from prelevement p2
                   where p2.facture = p.facture
                  ) and
     p.line = (select max(p2.line)
               from prelevement p2
               where p2.facture = p.facture and p2.date_op = p.date_op
              ) ;

And in MySQL 8.0, you can use window functions:

select p.*
from (select p.*, row_number() over (partition by facture order by date_op desc, line desc) as seqnum
      from prelevement p
     ) p
where seqnum = 1;

Here is a db<>fiddle for all three solutions.

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30575

This would work:

CREATE TABLE prelevement (
  line int,
  facture varchar(30),
  date_op varchar(30),
  code_op varchar(30)
);

✓

        insert into prelevement 
(line,facture,date_op,code_op)
values 
(1,'F1','2019-02-20','PREL'),
(2,'F1','2019-02-20','CART'),
(3,'F1','2019-02-20','REJE'),
(8,'F1','2019-02-19','PREL'),
(2,'F2','2019-02-15','PREL'),
(1,'F2','2017-01-25','PREL'),
(1,'F3','2018-02-25','REJ'),
(2,'F3','2018-02-25','CART');

✓

select * from prelevement pp where facture in 
(
  select facture from (
    select * from prelevement p 
    where facture in ( 
      select facture from prelevement pin 
      group by facture
      having max(pin.date_op) = p.date_op 
    )
  ) t
  group by facture
  having max(t.line) = pp.line
) 

line | facture | date_op    | code_op
---: | :------ | :--------- | :------
   3 | F1      | 2019-02-20 | REJE   
   2 | F2      | 2019-02-15 | PREL   
   2 | F3      | 2018-02-25 | CART

db<>fiddle here

Upvotes: 0

Related Questions