There is a way to get the first row after UNION?

Question

I´m trying to get the most repeated values in two columns for me this query is running ok and I´m getting the results as expected. But I only want the first row it´s possible to get this with ROWNUM =1? Or something similar?

My environment is Oracle 11 and SQL Developer.

My code:

SELECT AIR_ARB, COUNT(*) FROM
(
SELECT AIR_ARB FROM AIR_INFO  WHERE  V_VX IN (910208,910209,9......) AND V_Y IN
(826369,826370,8.....)
UNION ALL
SELECT AIR_MAT FROM AIR_INFO WHERE  V_VX IN (910208,910209,9......) AND V_Y IN 
(826369,826370,8.....)

) t
GROUP BY AIR_ARB
ORDER BY COUNT (*) desc

My results:

AIR_ARB COUNT(*)
null     64
100      23
0        20
72       15
143       3
43        3

Answer 1

You need an additional subquery:

SELECT a.*
FROM (SELECT AIR_ARB, COUNT(*) as cnt
      FROM (SELECT AIR_ARB FROM AIR_INFO  WHERE  V_VX IN (910208,910209,9......) AND V_Y IN (826369,826370,8.....)
            UNION ALL
            SELECT AIR_MAT FROM AIR_INFO WHERE  V_VX IN (910208,910209,9......) AND V_Y IN(826369,826370,8.....)
           ) t
      GROUP BY AIR_ARB
      ORDER BY COUNT (*) desc
     ) a
WHERE rownum = 1;