CODEWITHSUNDEEP
mysqlsqlselectjoin
user3117337
user3117337

Reputation: 223

select a column with the highest id which have common fields

i have a table named iview:

gpreq_id    a_id   m_id     rcv_qty
1           100      4         0
2           100      4         1
3           100      5         4
4           101      4         1
5           101      4         10
6           101      4         1

how can i select this that the m_id in the a_id's has the highest gpreq_id?

like:

gpreq_id    a_id   m_id     rcv_qty
2           100      4         1
3           100      5         4
6           101      4         1

Upvotes: 0

Views: 43

Answers (2)

user2989408
user2989408

Reputation: 3137

Try something like 

SELECT i1.*
FROM iview as i1
WHERE i1.gpreq_id IN (SELECT MAX(gpreq_id) 
                     FROM iview as i2 
                     GROUP BY i2.a_id, i2.m_id)

Here is the SQL FIDDLE

Upvotes: 1

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15881

First find max value for each a_id, m_id pair and then join to iview:

select i.* 
 from iview as i 
  inner join (
  select a_id, m_id, max(gpreq_id) as max_gpreq_id
   from iview 
  group by a_id, m_id
) as mi on (mi.max_gpreq_id = i.pgreq_ie)

Upvotes: 1

Related Questions