Ive got a problem with a MySQL query.
Let's say, we have two tables:
id qty
1......1
2......1
3......1
4......3
and
id qty
1......2
2......1
6......1
7......2
What I actually need to get is a result filtered by id, so there are no duplicate id's. If they are two same id's, only id whith a higher qty number will be considered. So in and ideal situation I would get this result:
id qty
1......2
2......1
3......1
4......3
6......1
7......2
What Ive tried so far is a UNION SELECT, which works fine, but when it comes across a duplicate ID it wont remove duplicates, as it removes just duplicates rows. GROUP BY didnt help either. I could still sort this out through an array in PHP, but I would love having this sorted at MySQL level if possible.
Thanks everyone for you help, it's much appreciated!