CODEWITHSUNDEEP
mysqlsqllimit
Tupic
Tupic

Reputation: 565

mysql get last values grouped

i have a table like this

id  orderid type  date
20  831     1   2016-10-26 15:09:25
22  1410    1   2016-10-26 15:09:27
23  1779    1   2016-10-10 15:09:29
26  1779    2   2016-10-10 15:09:29
24  1809    1   2016-10-26 15:09:30

and i want to get from every order the last type. So order 1779 should be type 2

can someone help plz?

Upvotes: 1

Views: 32

Answers (3)

Prateek Mittal
Prateek Mittal

Reputation: 41

SELECT distinct t1.orderid , (select max(type) from table where orderid=t1.orderid) from table t1

Try this

Upvotes: 0

Pham X. Bach
Pham X. Bach

Reputation: 5432

In case you mean last type is the type with largest value, The query is just simple like this

SELECT orderid, 
    MAX(type) AS last_type
FROM table_name
GROUP BY orderid;

Upvotes: 1

Teja
Teja

Reputation: 13524

SELECT id,orderid,type,date 
  FROM
     (
       SELECT *,
              ROW_NUMBER() OVER ( PARTITION BY orderid ORDER BY type DESC ) AS rn
         FROM yourtable
      ) 
 WHERE rn = 1;

Upvotes: 0

Related Questions