SQL: Prioritizing MAX Calculations-CodePudding

I'm using the following code below to pull the latest record in date/seq for each item, one row for each item. The issue I'm finding is that I believe I need to prioritize date as the MAX, then calculate the max seq afterwards. The second table shows the issue I'm having for item 5356262 (and a few others), which this sku is not pulling in with any data to the query... I believe because the max SEQ is on a different line than the max date so it's getting confused?

Any help would be appreciated. Thanks.

General data example:

item	exdt	seq	M	L
35435353	6/2/2021	3	$52	$15
64363663	5/24/2021	1	$33	$5
75474747	7/24/2021	2	$13	$3

Issue Example (trying to pick up only the 7/24/2021 record).

item	exdt	seq	M	L
5356262	6/2/2021	3	$52	$15
5356262	5/24/2021	1	$33	$5
5356262	7/24/2021	1	$13	$3

SELECT *
(SELECT item, cpp, exdt, seq, tvt_1   tft_1 AS ttot_1
     FROM      dbo.sample123) AS p
PIVOT (SUM(ttot_1) FOR cpp IN ([M], [L], [OH]])) AS pvtc
WHERE  (exdt =
      (SELECT MAX(exdt) AS exdtmax
       FROM      dbo.sample123 t2
       WHERE   (LTRIM(t2.item)=LTRIM(pvtc.item))))
AND (seqn =
      (SELECT MAX(seq) AS seqmax
      FROM      dbo.sample123 AS t2
      WHERE LTRIM(t2.item)=LTRIM(pvtc.item)))

CodePudding user response：

Simplest approach is probably to use something like ROW_NUMBER with an ORDER BY clause to control the way you determine the first row. Something like this:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY item ORDER BY exdt DESC, seq ASC) as rownum
  FROM (
    SELECT item, cpp, exdt, seq, tvt_1   tft_1 AS ttot_1
    FROM dbo.sample123
  ) AS p
  PIVOT (SUM(ttot_1) FOR cpp IN ([M], [L], [OH])) AS pvtc
) a
WHERE rownum = 1

CodePudding user response：

Are you trying to simply get the tuple with the maximum date per each group of rows? Then do something else?

THen brake the problem into smaller problems and solve each individually:

This should do it:

Assume you want to get the maximum of of b for each group of attribute a (it can be several attributes):

select * 
from table 
where (a, b) in (select a, max(b) from table group by a));

if you need to then select the tuple with the maximum attribute c then you can do wrap the query above into a subquery:

with t as (select * 
   from table 
   where (a, b) in (select a, max(b) from table group by a)))

select * from t where c in (select max(c) from t)

;