Geek Answers Handbook

Create aggregate function for GROUP BY?

I have a query that displays the results as follows:

Person Food ====== ====== Abner Apple Beth Banana Beth Peach Carlos Grape Carlos Kiwi Carlos Strawberry . . . .

Namely, it connects a person with one or more products. I would like the functionality of the aggregate function to choose food, but none of the built-in aggregates (SUM, MAX, MIN, etc.) really apply to the situation.

If I have a function F (), which, given the list of products, either:

  • provides an ordered list of products based on some optimization
  • provides optimal unique selection from the list

Is there any way to apply GROUP BY to it?

Does anyone have an example request that does this differently in case it's a dream? [/ p>

= = = =

EDIT: let's say that of the above options, the optimal list is {Kiwi, Grape, Banana, Strawberry, Apple, Peach}, then the output should say:

 Abner Apple Beth Banana Carlos Kiwi

those. one entry per person, and Food for each is the first entry in the optimal list of Food associated with this Person in the source table.

+4
source share
4 answers
 SELECT DISTINCT t.Person, f.Food FROM MyTable AS t CROSS APPLY( SELECT TOP 1 Food FROM MyTable AS t1 WHERE t.Person = t1.Person ORDER BY <your method of rating and choosing goes here> ) AS f
+2
source

Do you want you to concatenate these products?

Try the following:

 select p.Person, stuff((select ', ' + f.Food [text()] from Food f where f.PersonId = p.Id order by f.Food for xml path('')),1,2,'') [Foods] from Person p
+1
source

Please note that in my queries here you can replace the OptimalFood table OptimalFood your function of returning to the F () rowset (if I understand correctly).

 WITH PersonFood (Name, Food) AS ( SELECT 'Abner', 'Apple' UNION ALL SELECT 'Beth', 'Banana' UNION ALL SELECT 'Beth', 'Peach' UNION ALL SELECT 'Carlos', 'Grape' UNION ALL SELECT 'Carlos', 'Kiwi' UNION ALL SELECT 'Carlos', 'Strawberry' UNION ALL SELECT 'Delilah', 'Passionfruit' ), OptimalFood (Priority, Food) AS ( SELECT 1, 'Kiwi' UNION ALL SELECT 2, 'Grape' UNION ALL SELECT 3, 'Banana' UNION ALL SELECT 4, 'Strawberry' UNION ALL SELECT 5, 'Apple' UNION ALL SELECT 6, 'Peach' ), Choices AS ( SELECT Selector = Row_Number() OVER (PARTITION BY F.Name ORDER BY Coalesce(O.Priority, 2147483647)), F.Name, Food = Coalesce(O.Food, '<None>') FROM PersonFood F LEFT JOIN OptimalFood O ON F.Food = O.Food ) SELECT Name, Food FROM Choices WHERE Selector = 1;

If you have a table with all the people listed once already, this might be the best way:

 WITH PersonFood (Name, Food) AS ( SELECT 'Abner', 'Apple' UNION ALL SELECT 'Beth', 'Banana' UNION ALL SELECT 'Beth', 'Peach' UNION ALL SELECT 'Carlos', 'Grape' UNION ALL SELECT 'Carlos', 'Kiwi' UNION ALL SELECT 'Carlos', 'Strawberry' UNION ALL SELECT 'Delilah', 'Passionfruit' ), OptimalFood (Priority, Food) AS ( SELECT 1, 'Kiwi' UNION ALL SELECT 2, 'Grape' UNION ALL SELECT 3, 'Banana' UNION ALL SELECT 4, 'Strawberry' UNION ALL SELECT 5, 'Apple' UNION ALL SELECT 6, 'Peach' ), Person AS ( SELECT DISTINCT Name FROM PersonFood ) SELECT P.Name, Food = Coalesce(X.Food, '<None>') FROM Person P OUTER APPLY ( SELECT TOP 1 O.Food FROM PersonFood F INNER JOIN OptimalFood O ON F.Food = O.Food WHERE P.Name = F.Name ORDER BY O.Priority ) X;

I would expect that in all these cases you are actually using numeric keys, not varchar strings. I hope so. :)

+1
source

You can go without a group with this:

 SELECT DISTINCT Person, F(Person) AS FoodFunction FROM TableName

You will need to change the F () function so that the person (and not the food list) receives the list of products:

 SELECT Food FROM TableName WHERE Person = @Person
0
source

More articles:


All Articles