SQL Server Selecting MAX on column then MAX from column that is dependent on first value

ahy6op9u 于 2023-03-22 发布在其他

关注(0)|答案(3)|浏览(148)

I have table like this:

CREATE TABLE #Test
(
    ParentID int,
    DateCreated DATETIME,
    ItemNo int
)

INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (1,'2008-10-01 00:00:00.000',0)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (1,'2008-10-01 00:00:00.000',1)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (1,'2008-05-01 00:00:00.000',2)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (1,'2008-05-01 00:00:00.000',3)

INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (2,'2008-06-01 00:00:00.000',3)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (2,'2008-06-01 00:00:00.000',4)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (2,'2008-04-01 00:00:00.000',6)
INSERT INTO #Test(ParentID, DateCreated, ItemNo) VALUES  (2,'2008-04-01 00:00:00.000',8)

I need a way of selecting highest DateCreated with highest ItemNo on same parentID, and if it is possible to use solution in query like this:

SELECT * 
FROM #Test t
  JOIN
  (
    If I could get maximum row here somehow that would be great
  ) maxt
  ON t.ParentID = maxt.ParentID 
  JOIN SomeOtherTable sot
  ON sot.DateCreated = maxt.MaxDateCreated
  AND sot.ItemNo = maxt.MaxItemNo
GROUP BY
  sot.Something

Just to clarify what results should look like:

ParentID        DateCreated           ItemNo   ParentID      MaxDateCreated    MaxItemNo
  1,       '2008-10-01 00:00:00.000'  ,0         1,      '2008-10-01 00:00:00.000',1
  1,       '2008-10-01 00:00:00.000'  ,1         1,      '2008-10-01 00:00:00.000',1
  1,       '2008-05-01 00:00:00.000'  ,2         1,      '2008-10-01 00:00:00.000',1
  1,       '2008-05-01 00:00:00.000'  ,3         1,      '2008-10-01 00:00:00.000',1
  2,       '2008-06-01 00:00:00.000'  ,3         2,      '2008-06-01 00:00:00.000',4
  2,       '2008-06-01 00:00:00.000'  ,4         2,      '2008-06-01 00:00:00.000',4
  2,       '2008-04-01 00:00:00.000'  ,6         2,      '2008-06-01 00:00:00.000',4
  2,       '2008-04-01 00:00:00.000'  ,8         2,      '2008-06-01 00:00:00.000',4

sql-server

来源：https://stackoverflow.com/questions/18123172/selecting-max-on-column-then-max-from-column-that-is-dependent-on-first-value

3条答案

按热度按时间

cedebl8k1#

If you need the Maximum of DateCreated and the Maximum ItemNo for this DateCreated:

select ParentId,
       DateCreated as MaxDateCreated,
       ItemNo as MaxItemNo
     from 
      (select PArentID,DateCreated,ItemNo, 
         Row_Number() OVER (PARTITION BY ParentID 
                             ORDER BY DateCreated DESC, 
                                      ItemNo Desc) as RN
         from #Test          
      ) t3
     where RN=1

SQLFillde demo

UPD

And to get results as mentioned in the question you should join this with #TEST like:

SELECT * 
FROM Test t
  JOIN
  (
select ParentId,
           DateCreated as MaxDateCreated,
           ItemNo as MaxItemNo
         from 
          (select PArentID,DateCreated,ItemNo, 
             Row_Number() OVER (PARTITION BY ParentID 
                                 ORDER BY DateCreated DESC, 
                                          ItemNo Desc) as RN
             from test          
          ) t3
         where RN=1
  ) maxt
  ON t.ParentID = maxt.ParentID

SQLFiddle demo

赞(0）回复(0）举报 2023-03-22

68bkxrlz2#

SELECT 
DateCreated, ItemNo, ParentID,
MAX(DateCreated) over (PARTITION BY ParentID)  MaxDateCreated, 
MAX(itemNo*case when rn = 1 then 1 end) over (PARTITION BY parentid) MaxItemNo
FROM
(
  SELECT DateCreated, ItemNo, ParentID, 
  row_number() over (PARTITION BY parentid order by DateCreated desc, ItemNo desc) rn 
  FROM #test
) a

赞(0）回复(0）举报 2023-03-22

93ze6v8z3#

Simpler solution: Using max(concat(first_column,second_column))

The idea is concat two column then sort it using max.

Example: select max(concat(first_column,second_column)) from table

赞(0）回复(0）举报 2023-03-22