ANSI Alternatives to TOP WITH TIES

Question

I have a working query in T-SQL and I was attempting to use it on a programme to upload data from my SQL Server to the programme for reporting purposes however I found out that the programme does not support T-SQL and it mainly uses ANSI. Would anyone be able to help me find an alternative to the 'TOP 1 WITH TIES' part of the query for ANSI?

I am trying to get the newest cost prices for all products from different tables and I need the WITH TIES so as to not have the query exclude basically everything as a most of the products have the same price as others.

Here is the current query that works perfectly (To my knowledge) in T-SQL:

SELECT TOP 1 with ties
   LastCostDate as LastDate,
   SiteID,
   [d1].UnitsInOuter,
   [PLUGroup2].Description as GroupDescription,
   [d1].PLUID,
   [d1].Description,
   Cost,
   Null as Price,
   Null as Units,
   Null as RetailValue
FROM
  (select   LastCostDate,
        [LocStock].SiteID,
        [LastDate].UnitsInOuter,
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([PLUCostHistory].ChangeDate) as LastCostDate,max(PLUOuterSize.UnitsInOuter) as UnitsInOuter, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID Inner Join PLUOuterSize on PLUOuterSize.PLUOuterSizeID=PLUCostHistory.PLUOuterSizeID group by [PLUCostHistory].PLUID, [PLU].Description ) as LastDate

Inner Join (select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.ChangeDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID

UNION

select  LastCostDate,
        [DelMast].SiteID,       
        [LastDate].UnitsInOuter,
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([DelMast].DeliveryDate) as LastCostDate,max(PLUOuterSize.UnitsInOuter) as UnitsInOuter, [DelDets].PLUID, [PLU].Description From [DelMast] Inner Join [DelDets]
        on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU] on [PLU].PLUID=[DelDets].PLUID Inner Join [PLUGroupRef]
        on [DelDets].PLUID=[PLUGroupRef].PLUID Inner Join [PLUGroup2]
        on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID Inner Join PLUOuterSize on PLUOuterSize.PLUOuterSizeID=DelDets.PLUOuterSizeID group by [DelDets].PLUID, [PLU].Description) as LastDate

INNER Join (select [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description, sum([DelDets].Cost) as Cost From [DelMast] Inner Join [DelDets] on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU]
        on [PLU].PLUID=[DelDets].PLUID group by [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.DeliveryDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID
Inner Join [DelMast] 
        on [LocStock].SiteID=[DelMast].SiteID   ) as d1
Inner Join [PLUGroupRef]
        on [PLUGroupRef].PLUID=[d1].PLUID
Inner Join [PLUGroup2]
        on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID

/*where SiteID like '%BALYS%' and [PLUGroup2].Description like '%TOBACCO%' */
ORDER BY ROW_NUMBER() OVER (PARTITION BY [d1].Description,SiteID ORDER BY LastCostDate DESC)

You can always move ROW_NUMBER to column, wrap the whole thing in subquery and then do WHERE rownumbercolumn = 1 — siggemannen, Mar 08 '23 at 15:10
The "top" answer in [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) is ANSI compliant. — Thom A, Mar 08 '23 at 15:21
@siggemannen wouldn't that just get the absolute top row instead of the top row of every product as only one of the rows would have the row_number of 1? — ted15555, Mar 08 '23 at 15:46
No, since you partition by some fields, every first product in those groups will have a row = 1 — siggemannen, Mar 08 '23 at 15:52
You need to remove TOP 1 altogether after you do the row_number thing, maybe it wasn't clear :) — siggemannen, Mar 08 '23 at 15:53
@siggemannen Ah yes, I completely forgot about the partition in the Row_Number function! Thanks! — ted15555, Mar 08 '23 at 16:06

jarlh · Answer 1 · 2023-03-08T15:20:54.440

ANSI/ISO SQL has:

OFFSET 0 ROWS FETCH FIRST 1 ROW WITH TIES

OFFSET 0 ROWS is a SQL Server specific requirement. The OFFSET clause is supposed to be optional.

The following features outside Core SQL-2016 are used:
F861, Top-level <result offset clause> in <query expression>
F867, FETCH FIRST clause: WITH TIES option
F857, Top-level <fetch first clause> in <query expression>
F850, Top-level <order by clause> in <query expression>

(Comes after the ORDER BY clause.)

ANSI Alternatives to TOP WITH TIES

1 Answers1