[Subquery] Pick only one row from table that many rows match few conditions

[adamjank]

Hello,

As the SQL oriented user I cannot provide the solution for the QV of getting the same results as I can manage in the SQL Server, so...

I'll be grateful for the solution or the ways to solve it. I want to get the biggest value from the Exc table, but only rows that match the conditions are taken into account; for keeping the transparency of the process I'd like to list the Row Number to show which condition has been matched.

I've provided sample file for the testing an there's the pseudoSQL below that it's the solution for the this problem.
Furthermore I'd like to not use the left join as the one row can match many conditions, so it'll duplicate the data (picking the proper rows from the table with duplicates isn't the best idea). The ApplyMap() won't solve it as the condition sequence has to be chosen (ApplyMap(..,ApplyMap(..,ApplyMap())) etc. for the synthetic keys).

I hope there exists some efficient solution.

Thank you in advance

SELECT

  Vegs.Color,

  Vegs.Country,

  Vegs.Name,

  Vegs.Type,

  Coalesce((SELECT Top 1 Exc.Value //get the highest Value

       FROM Exc

       WHERE  IIF(Exc.Color = '*', True, Exc.Color=Vegs.Color) AND

                      IIF(Exc.Country = '*', True, Exc.Country=Vegs.Country) AND

                      IIF(Exc.Name = '*', True, Exc.Name=Vegs.Name) AND

                      IIF(Exc.Type = '*', True, Exc.Type=Vegs.Type)

                      ORDER BY Exc.Value Desc

                      ),0) as Answer.Value,

  STUFF(( SELECT ',' + Exc.No //iterate via rows that match the conditions in the Exc table

       FROM Exc

       WHERE  IIF(Exc.Color = '*', True, Exc.Color=Vegs.Color) AND

                      IIF(Exc.Country = '*', True, Exc.Country=Vegs.Country) AND

                      IIF(Exc.Name = '*', True, Exc.Name=Vegs.Name) AND

                      IIF(Exc.Type = '*', True, Exc.Type=Vegs.Type)

                      ORDER BY Exc.Value Desc

                      FOR XML PATH('')

                      ), 1, 1, '') as as Answer.No

FROM Vegs

[maxgro · Accepted solution]

The idea here is to join (full outer) the 2 tables and then read the result table to check the rules

Vegs:

LOAD * Inline

[Type, Name, Color, Country

Vege, Cucumber, Green, Spain

Vege, Tomato, Red, Spain

Fruit, Orange, Orange, Turkey

Fruit, Apple, Green, Italy

Vege, Potato, Yellow, Italy

Vege, Cucumber, Green, Italy];

//Exc:

join (Vegs)

LOAD * INLINE

[VNo, VType, VName, VColor, VCountry, VValue

1,*, *, *, Spain, 10

2,*,* , Red,* ,20

3,Fruit, *, *, *,30

4,*, Apple, *,Italy, 40

];

Vegs2:

LOAD

     *, -1*((R1+R2+R3+R4)=-4) as R;

NoConcatenate load

     Type, Name, Color, Country,

     VNo,

     (Type=VType or VType='*') as R1, 

     (Name=VName or VName='*') as R2,

     (Color=VColor or VColor='*') as R3,

     (Country=VCountry or VCountry='*') as R4,

     VValue

Resident Vegs;

DROP Table Vegs;

Vegs3:

LOAD

     Type, Name, Color, Country, if(R,VNo) as VNo, if(R, VValue) as VValue

Resident Vegs2

Where Type <> Peek('Type') or Name <> Peek('Name') or Color <> Peek('Color') or Country <> Peek('Country')

Order By Type, Name, Color, Country, R desc, VValue desc;

DROP Table Vegs2;

[adamjank]

Thank you maxgro‌!
It's working solution, unfortunately I have to make it via joins.

Is it possible to make some kind of synthetic key to keep the Exc Table somehow linked?
I'd like to choose one item in the Vegs3 table and see only the rows with matched conditions.

Thank you in advance

[maxgro]

maybe:

add the Exc table at the end of my previous script, the association (join) is by VNo

Exc:

LOAD * INLINE

[VNo, VType, VName, VColor, VCountry, Value

1,*, *, *, Spain, 10

2,*,* , Red,* ,20

3,Fruit, *, *, *,30

4,*, Apple, *,Italy, 40

];