3 Replies Latest reply: Dec 9, 2016 2:11 PM by Massimo Grossi RSS

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

    Adam Jankowski

      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

      Screen.PNG

       

      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

        • Re: [Subquery] Pick only one row from table that many rows match few conditions
          Massimo Grossi

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

           

          1.png

           

          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;