CODEWITHSUNDEEP
sqlms-accessselect
CMarc
CMarc

Reputation: 23

MS Access Select Statement with Top X Distinct with Order By

I'm trying to write a MS Access query that will return the top X distinct production orders as ordered by SummaryEntryNumber descending, but when trying to use distinct or group by the data is being returned out of order.

Table: tblSummary

SummaryEntryNumber | ProductionOrder
2720               | 81184419
2719               | 81184419
2718               | 81184418
2717               | 81184418
2716               | 81184418
2715               | 81184418
2714               | 81184418
2713               | 81184416
2712               | 81170839
2711               | 81170839
2710               | 81170839
2709               | 81170839
2708               | 81170665
2707               | 81170665

 SELECT TOP 3 ProductionOrder FROM
 (
        SELECT SummaryEntryNumber, ProductionOrder
        FROM tblSummary
        ORDER BY SummaryEntryNumber DESC
 )

Returns: Order is correct, but contains duplicates:

ProductionOrder
81184419
81184419
81184418

 SELECT DISTINCT TOP 3 ProductionOrder FROM
 (
        SELECT SummaryEntryNumber, ProductionOrder
        FROM tblSummary
        ORDER BY SummaryEntryNumber DESC
 )

Returns: No duplicates, but order is not correct

ProductionOrder
81147954
81149278
81149279

I'm expecting it the query to return:

ProductionOrder
81184419
81184418
81184416

Any help would be appreciated!

Upvotes: 0

Views: 77

Answers (4)

Lee Mac
Lee Mac

Reputation: 16015

I think the following should suffice:

select top 3 t.productionorder
from tblsummary t
group by t.productionorder
order by max(t.summaryentrynumber) desc

Here, each distinct productionorder value is ordered by the maximum summaryentrynumber within each group of summaryentrynumber values for a given productionorder value, producing the desired result -

productionorder
81184419
81184418
81184416

Upvotes: 0

Inot
Inot

Reputation: 133

This is a little bit tricky, but you should also use the DISTINCT clause in the subquery for it to work as expected:

SELECT DISTINCT TOP 3 ProductionOrder FROM
 (
        SELECT DISTINCT SummaryEntryNumber, ProductionOrder
        FROM tblSummary
        ORDER BY SummaryEntryNumber DESC
 )

This works fine.

Upvotes: 0

forpas
forpas

Reputation: 164089

First group by ProductionOrder to get the maximum SummaryEntryNumber for each ProductionOrder and then sort the result and get TOP 3:

SELECT TOP 3 t.ProductionOrder
FROM (
  SELECT ProductionOrder, MAX(SummaryEntryNumber) AS MaxSummaryEntryNumber
  FROM tblSummary 
  GROUP BY ProductionOrder
) AS t
ORDER BY t.MaxSummaryEntryNumber DESC

Results:

ProductionOrder
81184419
81184418
81184416

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

I think you need an explicit ORDER BY clause for ProductionOrder also

SELECT DISTINCT TOP 3 ProductionOrder
FROM (SELECT DISTINCT SummaryEntryNumber, ProductionOrder
      FROM tblSummary
      ORDER BY SummaryEntryNumber DESC, ProductionOrder DESC)

Upvotes: 0

Related Questions