Producing the biggest tradeNumber in UNION

[joon1]

code France America flower
(2) America France soap
(3) China France tea[/code]I have a table named “trade” like the above.
and SQL like the below.

select tradeNumber, counterCountry, item from( (select tradeNumber, import as counterCountry, item from trade where export='$country' ORDER BY tradeNumber desc limit 1) union (select tradeNumber, export as counterCountry, item from trade where import='$country' ORDER BY tradeNumber desc limit 1) ) as unionTable GROUP BY counterCountry ORDER BY counterCountry The code above produces the result below when the value of $country is “France”.

code America flower
(3) China tea[/code]I like to produce my target result below.

code America soap
(3) China tea[/code]How can I produce my target result above?

[Mittineague]

If you run the two queries separately do you get the results you expect to be getting?

select tradeNumber, import as counterCountry, item
from trade
where export='$country' ORDER BY tradeNumber desc limit 1

and

select tradeNumber, export as counterCountry, item
from trade
where import='$country' ORDER BY tradeNumber desc limit 1

[joon1]

[quote=“Mittineague, post:2, topic:275213”]
select tradeNumber, import as counterCountry, item
from trade
where export=‘$country’ ORDER BY tradeNumber desc limit 1
[/quote]The code above produces (1) America,

[quote=“Mittineague, post:2, topic:275213”]select tradeNumber, export as counterCountry, item
from trade
where import=‘$country’ ORDER BY tradeNumber desc limit 1[/quote]The code above produces (3) China.
So now I understand why the origin SQL produces the result below.

code America flower
(3) China tea
[/code]

Then, How can I produces my target result below?

code America soap
(3) China tea
[/code]I like to produce the most recent trade record of each counterCountry of France regardless it is export or import.

[Mittineague]

@joon1

How can I produces my target result below?

With this query?

select tradeNumber, export as counterCountry, item
from trade
where import='$country' ORDER BY tradeNumber desc limit 2
GROUP BY counterCountry ORDER BY counterCountry

[joon1]

@Mittineague

select tradeNumber, export as counterCountry, item
from trade
where import=‘$country’ ORDER BY tradeNumber desc limit 2
GROUP BY counterCountry ORDER BY counterCountry

I am afraid that the code above produces “Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource”

[Mittineague]

That looks like a PHP error message from a problem using the obsolete mysql_ database extension.

How does it look running the query outside of PHP?

[r937]

please, before you go any further, fix this issue –

select tradeNumber, counterCountry, item
  from ...
GROUP BY counterCountry

the values for tradeNumber and item will be indeterminate

please learn how GROUP BY works