Selecting single Record From multiple similar results

Question

SELECT     TOP (100) PERCENT dbo.vwLatestEquipment.equipServID, dbo.tblEquipInvoice.invoiceDateSent, dbo.tblEquipment.equipManafacturer, 
                  dbo.tblEquipInvoice.contractNumber, dbo.tblEquipment.equipmentID, dbo.tblEquipment.equipStatus, dbo.tbl_work_locations.work_location, 
                  dbo.vwLatestEquipment.calibDueDate
FROM         dbo.vwLatestEquipment INNER JOIN
                  dbo.tblEquipLocations ON dbo.vwLatestEquipment.locationID = dbo.tblEquipLocations.locationID INNER JOIN
                  dbo.tblEquipInvoice ON dbo.tblEquipLocations.invoiceID = dbo.tblEquipInvoice.invoiceID INNER JOIN
                  dbo.tblEquipment ON dbo.vwLatestEquipment.equipServID = dbo.tblEquipment.equipServID INNER JOIN
                  dbo.tbl_work_locations ON dbo.tblEquipInvoice.workLocationID = dbo.tbl_work_locations.work_id
GROUP BY dbo.tbl_work_locations.work_location, dbo.vwLatestEquipment.equipServID, dbo.tblEquipInvoice.invoiceDateSent, dbo.tblEquipment.equipManafacturer, 
                  dbo.tblEquipInvoice.contractNumber, dbo.tblEquipment.equipmentID,       dbo.tblEquipment.equipStatus, dbo.tbl_work_locations.work_location, 
                  dbo.vwLatestEquipment.calibDueDate
ORDER BY dbo.vwLatestEquipment.equipServID

This is one that's been passed to me from my successor, the view above is working fine for purpose, except it returns all the values for calibDateDue for every item of equipmentID All I want is for it to return the most recent calibDueDate for each item of equipment (equipmentID) any help would be appreciated today as I am fried and cant make much headway.

Regarding `TOP (100) PERCENT`: (http://stackoverflow.com/questions/1622878/why-use-select-top-100-percent) — Nikola Markovinović, Dec 04 '12 at 13:21

Mahmoud Gamal · Accepted Answer · 2012-12-04T13:07:24.590

I want is for it to return the most recent calibDueDate for each item of equipment (equipmentID).

Since you are using TOP (100) PERCENT, I will assume that you are using SQL Server. Then you can use the ranking function:

ROW_NUMBER() OVER(PARTITION BY equipmentID
                  ORDER BY calibDueDate DESC)

Then select only the row with the rank = 1, like so:

;WITH CTE 
AS
(
    SELECT 
      dbo.vwLatestEquipment.equipServID, 
      dbo.tblEquipInvoice.invoiceDateSent, 
      dbo.tblEquipment.equipManafacturer, 
      dbo.tblEquipInvoice.contractNumber, 
      dbo.tblEquipment.equipmentID, 
      dbo.tblEquipment.equipStatus, 
      dbo.tbl_work_locations.work_location, 
      dbo.vwLatestEquipment.calibDueDate,
      ROW_NUMBER() OVER(PARTITION BY equipmentID 
                        ORDER BY calibDueDate DESC) row_num
    FROM dbo.vwLatestEquipment 
    INNER JOIN dbo.tblEquipLocations 
            ON dbo.vwLatestEquipment.locationID=dbo.tblEquipLocations.locationID 
    INNER JOIN dbo.tblEquipInvoice
            ON dbo.tblEquipLocations.invoiceID=dbo.tblEquipInvoice.invoiceID 
    INNER JOIN dbo.tblEquipment 
            ON dbo.vwLatestEquipment.equipServID=dbo.tblEquipment.equipServID
    INNER JOIN dbo.tbl_work_locations 
            ON dbo.tblEquipInvoice.workLocationID=dbo.tbl_work_locations.work_id
) 
SELECT TOP (100) PERCENT *
FROM CTE 
WHERE row_num = 1
ORDER BY equipServID;

2nd last line should read WHERE row_num = 1 But apart from that it works fine, many thanks — Duncan Cook, Dec 04 '12 at 11:16
@DuncanCook - Fixed now. But apart from that, you're welcome :) — Mahmoud Gamal, Dec 04 '12 at 13:08

Selecting single Record From multiple similar results

1 Answers1