How to build the MYSQL query out of the tables given below?

Question

I have two tables which having master child relationship.

Master table:

master Id | Name 
  1       | name1
  2       | Name2

Child Table:

ChildId| MasterId | Detail |
  1    |  1       | det01  |
  2    |  1       | det02  |
  3    |  2       | det03  |
  4    |  2       | det04  |  
  5    |  2       | det05  |

What I want:

ChildId| MasterId | Detail |
  5    |  2       | det05  |
  2    |  1       | det02  |

this table is ordered by ChildId desc but having a single record against a MasterId. please let me know how to achieve this target.

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barmar, Mar 12 '14 at 12:13

dnl-blkv · Answer 1 · 2014-03-12T12:35:14.683

It might be what you are looking for:

SELECT MAX(child.ID) as ChildID, master.ID as MasterID, MAX(child.Detail) as Detail
FROM child 
INNER JOIN master 
ON child.MasterID = master.ID
GROUP BY master.ID
ORDER BY child.ID DESC;

The output would be in this case:

ChildID MasterID Detail
5       2        det05
2       1        det02

I could correct it with proper field and table names if you kindly provide me with them.

score 0 · Accepted Answer · answered Mar 12 '14 at 12:15

0

You need to use group by with aggregate function max()

select  c.masterid, max(c.childid), max(c.detail)
from child c inner join master m on c.masterid=m.`master id`
group by c.masterid
order by c.masterid desc

fiddle

answered Mar 12 '14 at 12:15

G one

2,679
2
14
18

score 0 · Answer 3 · answered Mar 12 '14 at 12:21

Try this:

SELECT MAX(C.ChildID) as ChildID,C.MasterID,C.Detail
FROM Child C,Master M
WHERE M.master_Id=C.MasterID
GROUP BY C.MasterID
ORDER BY C.MasterID DESC

Result:

CHILDID MASTERID    DETAIL
5        2          det03
2        1          det01

See result in SQL Fiddle.

How to build the MYSQL query out of the tables given below?

3 Answers3