CODEWITHSUNDEEP
phpmysql
Vahid Abdi
Vahid Abdi

Reputation: 9

how can i select these in mysql

Imagine that there are two tables called: Customers, Orders.

In Customers we have rows:

id=1 name=Alex key=12
id=2 name=Bob key=13

and in the Orders we have:

id=1 device=phone status=ordered key=12
id=2 device=phone status=delivered key=12
id=3 device=memory status=ordered key=13
id=4 device=memory status=returned key=13

Now I want it to choose the last one for each one of them in the Customers from Orders. Like it should choose Alex's information and the last row that is inserted for him in the Orders table.

How can I make a query for this?

Upvotes: 0

Views: 43

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Here is a way you can do it 

select 
c.*,
o.id,
o.device,
o.status
from customer c
join orders o on o.`key` = c.`key`
join (
  select max(id) as id,`key` from orders
  group by `key`
)o1 on o1.id=o.id

http://www.sqlfiddle.com/#!9/96885/2

Upvotes: 0

VolkerK
VolkerK

Reputation: 96159

Sounds like you're looking for the group-wise (key) maximum (id) as explained at https://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

I prefer (for no good reason) the LEFT JOIN variant over the uncorrelated sub-query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can do this with aggregation and a join:

select o.*
from customers c left join
     orders o
     on c.key = o.key left join
     (select o.key, max(id) as maxid
      from orders o
      group by o.key
     ) ok
     on o.id = oo.id;

The aggregation gets the latest value.

Note that key is a reserved word in MySQL (see here). That makes it a lousy column name, so you should change it.

Upvotes: 1

Related Questions