1

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,

Strawberry
  • 33,750
  • 13
  • 40
  • 57
KarSho
  • 5,699
  • 13
  • 45
  • 78
  • possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Kit Sunde Aug 10 '15 at 07:17

2 Answers2

1

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

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
0

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
Madhivanan
  • 13,470
  • 1
  • 24
  • 29