MYSQL DISTINCT on one column PLUS oher values I wish to retrieve ordno and invno where ordno is unique from two different tables (archived and none archived invoices). The closest I have come is using the following using UNION DISTINCT but I also need the invno - any help is welcome?

SELECT INInvoiceHeaders.ordno
FROM INInvoiceHeaders 
WHERE (MONTH(INInvoiceHeaders.invdate)=11  
  AND YEAR(INInvoiceHeaders.invdate)=2016) 
UNION DISTINCT
SELECT INArchivedInvoiceHeaders.ordno
FROM INArchivedInvoiceHeaders 
WHERE (MONTH(INArchivedInvoiceHeaders.invdate)=11 
  AND YEAR(INArchivedInvoiceHeaders.invdate)=2016)
GROUP BY ordno
SELECT DISTINCT(table1.ordno), table1.invno 
FROM ... 



You can't use DISTINCT like that, it is not a function. It's a modifier (of the SELECT or UNION clause) and it applies to all the row, not a single column.

You can use GROUP BY instead and MIN() function to find the smallest inventory number for each order, in each table.
Then use UNION (ALL, not DISTINCT) to get these results from both tables.
Then GROUP BY again in case some inventory numbers appear in both tables:

SELECT ordno, MIN(invno) AS invno
FROM
  ( SELECT ordno, MIN(invno) AS invno
    FROM INInvoiceHeaders 
    WHERE invdate &gt;= DATE '2016-11-01' 
      AND invdate  &lt; DATE '2016-11-01' + INTERVAL 1 MONTH

    UNION ALL

    SELECT ordno, MIN(invno)
    FROM INArchivedInvoiceHeaders 
    WHERE invdate &gt;= DATE '2016-11-01' 
      AND invdate  &lt; DATE '2016-11-01' + INTERVAL 1 MONTH
    GROUP BY ordno 
  ) AS t 
GROUP BY ordno ;