removing non-matching rows based on order in SQL within a WITH statement

Question

I've got a many-to-many setup where there are items and item names(based on languageID)

I want to retrieve all names for a set id, where the name is replace with an alternate name (same itemID, but different languageID) when name is NULL.

I've set up a table that receives all combinations of itemids and itemnames, even the missing ones, and have the name ordered by an hasName flag, that is set based on name existing to 0,1 or 2. 0 means languageId and name exist, 1 means only name exists, and 2 means neither. I then sort the results: ORDER BY itemId, hasName, languageId this works well enough, because the top 1 row of every itemid meats the critera, and I can just pull that.

However I still need to process other queries using the result, so this doesn't work well, because as soon as I use a WITH statement, the order cannot be used, so it breaks the functionality

What I'm using instead is a join, where I select the top 1 matching row on the ordered table

the problem there is that the time to execute goes up 10x

any ideas what else I could try?

using SQL server 10.50

the slow query:

SELECT  
    *, 
    (SELECT top 1 ItemName FROM ItemNameMultiLang x WHERE x.ItemId = tc.ItemId ORDER BY ItemID, hasName, LangID) AS ItemName

FROM ItemCategories tc
ORDER BY ItemId

What database is this? Why can't you use rownum (if Oracle, for example, or an equivalent) in the WITH clause to establish the order elsewhere? — Brian DeMilia, Feb 27 '14 at 00:42
You could also insert your results into a temp table instead of using a `with` - depending on the size. — Blorgbeard, Feb 27 '14 at 00:47
Can you post sample data (a few rows) and the desired output? — peterm, Feb 27 '14 at 02:10

score 2 · Accepted Answer · answered Feb 27 '14 at 02:23

2

One way to approach this is with row_number(), so you can get the first row from itemNameMultiLang, which is what you want:

SELECT tc.*, inml.ItemName
FROM ItemCategories tc left outer join
     (select inml.*, row_number() over (partition by inml.ItemId order by hasname, langId) as seqnum
      from ItemNameMultiLang
     ) inml
     on tc.ItemItem = inml.ItemId and
        inml.seqnum = 1
ORDER BY tc.ItemId;

answered Feb 27 '14 at 02:23

Gordon Linoff

1,242,037
58
646
786

good stuff, `row_number()` is clearly a better way to go, that way I'm only sorting once. – Daniel Feb 27 '14 at 23:06

removing non-matching rows based on order in SQL within a WITH statement

the slow query:

1 Answers1