CODEWITHSUNDEEP
phpmysqlsql
KarSho
KarSho

Reputation: 5746

MYSQL select recent record of each from

See my table(sample_table),

----------------------------- 
id    |   from    |    to   |
----------------------------- 
1     |    2      |    1    |
3     |    2      |    1    |
4     |    2      |    4    |
5     |    3      |    2    |
9     |    3      |    1    |
11    |    4      |    1    |
12    |    4      |    3    |
-----------------------------

For each from, I would like the row holding the most recent to, where to = 1

I mean I want only following,

----------------------------- 
id    |   from    |    to   |
----------------------------- 
3     |    2      |    1    |
9     |    3      |    1    |
11    |    4      |    1    |
-----------------------------

I Try following Query,

SELECT * FROM sample_table WHERE to = 1 GROUP BY from

It's giving first row of each. Help me.

Thanks,

Upvotes: 1

Views: 55

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

There are many ways to do it and here is one way 

select t1.* from sample_table t1
join(
  select max(id) as id,`from` from 
  sample_table where `to` = 1 
  group by `from`
)t2
on t1.id= t2.id and t1.`from` = t2.`from`

https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

select t1.id, t1.from, t1.to from table as t1 inner join
(
select to, from,min(id) as id from table 
where to=1 
group by to,from
) as t2 
on t1.to=t2.to and t1.id=2.id and t1.from=t2.from

Upvotes: 0

Related Questions