For example, there is a table with customers and there is a table with orders. In the query, I join these tables by customer id, but I end up with only a list of customers with summary data on orders. So, I need to get an order with a minimum amount in the query results. I can derive the minimum amount itself using the min function (column_c_summa), but how can I display, for example, another field from the table with orders corresponding to the minimum amount? Of course, I can do something like

SELECT п.покупатель , (SELECT Другое_поле FROM заказы зак WHERE зак.столбец_с_суммой = min(п.покупатель) AND зак.покупательИД = п.покупательИД) FROM покупатели п JOIN заказы з ON з.покупательИД = п.покупательИД GROUP BY п.покупатель

but it turns out a request for data that is already selected in the main request. My instinct tells me that window expressions can be used here, but I have never worked with them, I could not find any ready examples and cannot figure it out yet.

1 answer 1

My instinct tells me that you can use window expressions here.

True it tells you.

Here you can use the numbering function ROW_NUMBER :

 ;WITH заказы_покупателя AS ( SELECT з.покупательИД, з.столбец_с_суммой, з.Другое_поле, RN = ROW_NUMBER() OVER ( PARTITION BY з.покупательИД ORDER BY з.столбец_с_суммой) FROM заказы з ) SELECT п.покупатель, зп.столбец_с_суммой, зп.Другое_поле FROM покупатели п JOIN заказы_покупателя зп ON зп.покупательИД = п.покупательИД AND зп.RN = 1;

With the help of PARTITION BY set the window (we number the lines заказы з within the same з.покупательИД ). With the help of ORDER BY set the numbering order (by increasing the з.столбец_с_суммой ). In JOIN specify an additional condition, зп.RN = 1 , that will filter only one row for each зп.покупательИД (with the minimum value of a столбец_с_суммой ).

If a single customer may have several orders with the same minimum amount and you need to select a specific one of them, then you need to add an additional field to ORDER BY , for example, the order date or its ORDER BY з.столбец_с_суммой, з.заказИД .

If a single customer may have several orders with the same minimum amount and you need to select them all , then instead of ROW_NUMBER() you need to use DENSE_RANK() OVER (PARTITION BY з.покупательИД ORDER BY з.столбец_с_суммой) .