How can I exclude duplicates from this SELECT?

Question

Here's the data in the invoicehistory table.

invoicehistoryID WorkOrderNumber InvoiceDate

1                1               2017-06-13 12:00:49
2                1               2017-06-13 12:05:05
3                10              2017-06-14 12:32:59
4                10              2017-06-14 14:26:25
5                1               2017-06-14 14:26:54
6                1               2017-06-22 10:12:49

What SELECT query do I need to get only the latest InvoiceDate for each WorkOrderNumber, ordered by WorkOrderNumber? In other words, how can I get this:

invoicehistoryID WorkOrderNumber InvoiceDate
6                1               2017-06-22 10:12:49
4                10              2017-06-14 14:26:25

I haven't had success with DISTINCT. I haven't had success with LIMIT because my invoicehistory table will grow.

This almost works,

SELECT max(InvoiceDate),WorkOrderNumber FROM `invoicehistory` GROUP BY WorkOrderNumber

but doesn't get the invoicehistoryID.
This

SELECT max(InvoiceDate),WorkOrderNumber, invoicehistoryID FROM `invoicehistory` GROUP BY WorkOrderNumber, invoicehistoryID

returns all of the rows.

_...What SELECT query do I need to get..._ What queries have you tried so far? — B001ᛦ, Jun 22 '17 at 15:32
Think of data in terms of sets. you need the MAX(Invoicedate) for each work order number. This represents the set (A) you're after; but you need additional information (invoicehistoryID); which can be obtained by JOINING this set (A) back to your base set from invoicehistory. on the ordernumber and that invoice date. — xQbert, Jun 22 '17 at 15:33
Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — PM 77-1, Jun 22 '17 at 15:36

Javier · Answer 1 · 2017-06-22T15:44:12.007

0

Try:

SELECT invoicehistoryID, WorkOrderNumber, InvoiceDate
FROM (SELECT invoicehistoryID, WorkOrderNumber, InvoiceDate
  FROM TABLE
  ORDER BY WorkOrderNumber ASC, InvoiceDate DESC
) inv
GROUP BY WorkOrderNumber

edited Jun 22 '17 at 15:44

answered Jun 22 '17 at 15:34

Javier

38
4

Nope. I get "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY WorkOrderNumber LIMIT 0, 25' at line 4" – aswine Jun 22 '17 at 15:39
Sorry, my mistake, GROUP should be before ORDER. Also I noticed that, then, it will not work as said. Edited and try again. `SELECT invoicehistoryID, WorkOrderNumber, InvoiceDate FROM (SELECT invoicehistoryID, WorkOrderNumber, InvoiceDate FROM TABLE ORDER BY WorkOrderNumber ASC, InvoiceDate DESC ) inv GROUP BY WorkOrderNumber` – Javier Jun 22 '17 at 15:43
Still no. "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'inv.invoicehistoryID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" I already got xQbert's answer to work, but thanks anyway. – aswine Jun 22 '17 at 16:01

xQbert · Accepted Answer · 2017-06-22T15:51:36.233

0

Set D: is the max invoice date per work order.
Set IH is the entire Invoice history.

By Joining D to IH on the date and order nubmer we can get the missing invoicehistoryID and other information if needed.

SELECT IH.invoiceHistoryID, IH.WorkOrderNumber, IH.InvoiceDate
FROM InvoiceHistory IH
INNER JOIN (SELECT max(invoiceDate) mInvoiceDate, WorkOrderNumber
            FROM invoicehistory
            GROUP BY workorderNumber) D
 on D.mInvoiceDate = IH.InvoiceDate
and D.WorkOrderNumber = IH.workOrderNumber

Cross apply would be another way to achieve this if mySQL supported it

or use of window functions to assign a row number and limit the results to the newest date if mySQL supported them.

edited Jun 22 '17 at 15:51

answered Jun 22 '17 at 15:38

xQbert

34,733
2
41
62

Thank you. That wasn't exactly right, but it pointed me in the right direction. I ended up doing: `SELECT IH.WorkOrderNumber, IH.invoicehistoryID, IH.InvoiceDate FROM invoicehistory as IH INNER JOIN (SELECT max(invoiceDate) as maxInvoiceDate, WorkOrderNumber FROM invoicehistory GROUP BY workorderNumber) IH2 ON IH2.maxInvoiceDate = IH.InvoiceDate AND IH2.WorkOrderNumber = IH.workOrderNumber ORDER BY IH.WorkOrderNumber ASC` – aswine Jun 22 '17 at 15:49

score 0 · Answer 3 · answered Jun 22 '17 at 17:06

I didn't notice mySql until I had the answer in ms sql:

 CREATE TABLE #tmp(
    InvoiceHistoryID  INT,
    WorkOrderNumber INT,
    InvoiceDate datetime
    )

   INSERT INTO #tmp VALUES(1,1,'2017-06-13 12:00:49')
   INSERT INTO #tmp VALUES(2,1,'2017-06-13 12:05:05')
   INSERT INTO #tmp VALUES(3,10,'2017-06-14 12:32:59')
   INSERT INTO #tmp VALUES(4,10,'2017-06-14 12:32:59')
   INSERT INTO #tmp VALUES(5,1,'2017-06-14 14:26:54')
   INSERT INTO #tmp VALUES(6,1,'2017-06-22 10:12:49')


    SELECT 
       t.InvoiceHistoryID,
       t.WorkOrderNumber,
       m.InvoiceDate
    FROM #tmp t JOIN (SELECT MAX(InvoiceHistoryID) AS  InvoiceHistoryID, 
                             MAX(InvoiceDate) AS InvoiceDate
                    FROM #tmp 
                    GROUP BY WorkOrderNumber ) AS m 
    ON t.InvoiceHistoryID = m.InvoiceHistoryID                  

    DROP TABLE #tmp

How can I exclude duplicates from this SELECT?

3 Answers3