Задача по БД Northwind

Question

Показать 1 первый и 1 последний заказ (номер заказа и дату заказа) каждого продавца (достаточно вывести EmployeeID) без использования GROUP BY и UNION

SELECT DISTINCT EmployeeID, OrderDate,
       FIRST_VALUE(OrderID) OVER (
       PARTITION BY EmployeeID 
       ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS first_order, 
       LAST_VALUE(OrderID) OVER (
       PARTITION BY EmployeeID 
       ORDER BY OrderDate ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS last_order
FROM Orders

А как реализовать запрос не для 1-го первого и последнего заказа, а например для 2-х заказов?

Знаю, как отдельно вывести 2 первых заказа через ROW_NUMBER, но как к ним добавить еще и 2 последних?

WITH LastDate
AS
(
SELECT EmployeeID, OrderDate, OrderID,
       ROW_NUMBER() OVER (
       PARTITION BY EmployeeID 
       ORDER BY OrderDate 
       ) AS FD
FROM Orders
)

SELECT EmployeeID, OrderDate, OrderID
FROM LastDate
WHERE FD < 3

Answer 1

WITH LastDate
AS
(
SELECT EmployeeID, OrderDate, OrderID,
       ROW_NUMBER() OVER (
       PARTITION BY EmployeeID 
       ORDER BY OrderDate 
       ) AS FD, 
        ROW_NUMBER() OVER (
       PARTITION BY EmployeeID 
       ORDER BY OrderDate DESC
       ) AS LD
FROM Orders
)

SELECT EmployeeID, OrderDate, OrderID
FROM LastDate
WHERE FD < 3  or LD < 3

БЛОГ НА HUSL

Задача по БД Northwind

Ответы (1 шт):