How to Use "Partition By" or "Max"

MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

As Ponies says in a comment, you cannot mix OLAP functions with aggregate functions.

Perhaps it's easier to get the last completion date for each employee, and join that to a dataset containing the last completion date for each of the three targeted courses.

This is an untested idea that should hopefully put you down the right path:

  SELECT employee_number,
         course_code,
         MAX(course_completion_date) AS max_date,
         lcc.LAST_COURSE_COMPLETED
    FROM employee_course_completion ecc
         LEFT JOIN (
             SELECT employee_number,
                    MAX(course_completion_date) AS LAST_COURSE_COMPLETED
               FROM employee_course_completion
              WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
         ) lcc
         ON lcc.employee_number = ecc.employee_number
   WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code, lcc.LAST_COURSE_COMPLETED

How to use Partition By or Max?

select year, x,y
from (
      select year, x, y, max(year) over(partition by x) max_year
      from my data
      )
where  year = max_year

MAX() OVER PARTITION BY in Oracle SQL

Use window function ROW_NUMBER() OVER (PARTITION BY receipt_item ORDER BY receipt_date DESC) to assign a sequence number to each row. The row with the most recent receipt_date for a receipt_item will be numbered as 1.

WITH
-- various other subqueries above...

    AllData AS
    (
        SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE,
        ROW_NUMBER() OVER (PARTITION BY RECEIPT_ITEM ORDER BY RECEIPT_DATE DESC ) AS RN
        FROM tblVend
            INNER JOIN tblReceipt ON VEND_NUM = RECEIPT_VEND_NUM
        WHERE
            VEND_NUM IN ( '100','200')  AND RECEIPT_DATE >= '01-Jan-2017'
    )
   SELECT VEND_NUM, VEND_NAME, RECEIPT_NUM, RECEIPT_ITEM, RECEIPT_DATE
   FROM AllData WHERE RN = 1

MAX() OVER PARTITION BY not working as intended

I'm trying to get the ID of the item of the most expensive line, within an order.

You misunderstand the purpose of the order by clause to the window function; it is meant to defined the window frame, not to compare the values; max() gives you the maximum value of the expression given as argument within the window frame.

On the other hand, you want the itemKey of the most expensive order line. I think that first_value() would do what you want:

first_value(orderLines.itemKey) over(
    partition by orderHeader.orderKey 
    order by orderLines.price * orderLines.OrderedQty desc
) as [MaxPriceItem]

Only show value of Max rows with partition by?

One possible approach is using ROW_NUMBER:

SELECT 
    ID,
    CustomerID,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY ID DESC) = 1 THEN [Weight]
        ELSE Null
    END AS [Weight]
FROM #Orders
ORDER BY ID

Input:

CREATE TABLE #Orders (
    ID int,
    CustomerID int,
    [Weight] int
)
INSERT INTO #Orders
    (ID, CustomerID, [Weight])
VALUES
    (1, 11, 100),
    (2, 11, 17),
    (3, 11, 35),
    (4, 22, 26),
    (5, 22, 78),
    (6, 22, 10030)

Output:

ID  CustomerID  Weight
1   11          NULL
2   11          NULL
3   11          35
4   22          NULL
5   22          NULL
6   22          10030

Equivalent of max and over(partition by) in R for flattening

Using data.table:

library(data.table)
setDT(df)[
  , recorded_dt:=NULL][
  , lapply(.SD, \(x) sort(x, na.last = TRUE, decreasing = TRUE)[1])
  , by=.(ID, time0)]
##    ID      time0 day0 day1 day4 day30
## 1:  1 2009-01-01    A <NA>    B     D
## 2:  2 2005-02-02 <NA>    B <NA>  <NA>

The internal variable .SD represents a subset of the data.table including all columns except those included in the by=... clause. This is why we have to remove the column recorded_dt first.

Using PARTITION BY in a query to return max value of a select

If you are looking for the last value of the partition then you should use LAST_VALUE instead of MAX:

LASTVALUE(ConsignmentNumber) 
OVER 
(PARTITION BY SubAccountId, Reference3 ORDER By youOrderCol) AS LastConsignmentNumber

You also need to specify some field that determines order within each partition. Use this field in ORDER BY inside the OVER clause.

Your query could look like this:

;With LastValCTE AS (
   SELECT SubAccountId,
          Reference3,
          LASTVALUE(ConsignmentNumber) 
          OVER 
          (PARTITION BY SubAccountId, Reference3 
          ORDER By youOrderCol) AS LastConsignmentNumber
   FROM [CxGen].[dbo].[consignment] 
)
SELECT DISTINCT
    c1.SubAccountId,
    c1.Reference3,    
    c2.LastConsignmentNumber
FROM [CxGen].[dbo].[consignment] AS c1 
JOIN  LastValCTE AS c2 
   ON c1.SubAccountId = c2.SubAccountId AND c1.Reference3 = c2.Reference3
GROUP BY c1.SubAccountId, c1.Reference3
HAVING COUNT(*) > 1

This query returns the last value for each (SubAccountId, Reference3) group. A single record is returned for each group. Groups having only one record are filtered out.

If you want to get all records of each group then you can use the following query:

;With LastValCTE AS (
   SELECT SubAccountId,
          Reference3,
          ConsignmentNumber,
          COUNT(*) OVER (PARTITION BY SubAccountId, Reference3) AS cnt,
          LASTVALUE(ConsignmentNumber) 
          OVER 
          (PARTITION BY SubAccountId, Reference3 
          ORDER By youOrderCol) AS LastConsignmentNumber
   FROM [CxGen].[dbo].[consignment] 
)
SELECT SubAccountId,
       Reference3,
       ConsignmentNumber,
       LastConsignmentNumber
FROM LastValCTE 
WHERE cnt > 1 

Partition By - Sum all values Excluding Maximum Value

dense_rank() to identify the row with max weight, dr = 1 is rows with max weight

row_number() to differentiate the max weight row for Code = blank from others

---

with cte as
(
    select *, 
           dr = dense_rank() over (partition by ID order by [Weight] desc),
           rn = row_number() over (partition by ID order by [Weight] desc, Code desc)
    from   tbl
)
select *,
       ActWeight = case when dr = 1 and rn <> 1
                        then 0
                        when dr = 1 and rn = 1
                        then [Weight]
                           - sum(case when dr <> 1 then [Weight] else 0 end) over (partition by ID)
                        else [Weight]
                        end
from   cte

dbfiddle demo

How to use over - partition by query in SQL in order to get the current, average, and maximum value?

I think, I have solution for you. However, I am not sure about the answer will provide the correct result in different scenarios. Here is my code below=>
Please check the link=>DB-FIDDLE LINK.

WITH CTE AS
    (
      SELECT working_date,area,location,device, 
             COUNT(working_date) GrpCount
      FROM MYTable 
      GROUP BY working_date,area,location,device
    
    ),y AS
    (SELECT area,location,device,GrpCount,
           (SELECT GrpCount FROM CTE WHERE working_date<TO_DATE('21-06-2020','DD-MM-YYYY') ORDER BY working_date DESC LIMIT 1)  current_qty  
    FROM CTE
    )
    SELECT TO_DATE('21-06-2020','DD-MM-YYYY'),area,location,device, 
           MAX(current_qty) current_qty,
           string_agg(GrpCount::text, ',') avg_qty,
           Max(GrpCount) max_qty
    FROM Y
    GROUP BY area,location,device

Note:-Here, you can see, for current_qty I have used your input date 21-06-2020 like (SELECT GrpCount FROM CTE WHERE working_date<TO_DATE('21-06-2020','DD-MM-YYYY') ORDER BY working_date DESC LIMIT 1) current_qty to find current qty. And it gives me your expected result. Please check the code with different range of date range and data.

---

Related Topics