CODEWITHSUNDEEP
sqlsql-server
Nostromo
Nostromo

Reputation: 1264

How to get the value of column B depending on grouped column A

I use the Microsoft SQL Server 2019. From a table I'd like to get the value of column B depending on the value of column A being the maximum value over a certain where clause.

Let's make a small example.

Creating the table:

CREATE TABLE [tblDummy]
(
    [ID] [int] NOT NULL,
    [SubID] [int] NULL,
    [PointInTime] [datetime] NULL,
    [Value] [decimal](18, 6) NULL,

    CONSTRAINT [PK_tblDummy] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Inserting data into the table:

INSERT INTO tblDummy (ID, SubID, PointInTime, Value) 
VALUES (1, 1, {d '2020-01-01'}, 1.1),
       (2, 2, {d '2020-02-01'}, 1.2),
       (3, 3, {d '2020-03-01'}, 1.3),
       (4, 1, {d '2020-04-01'}, 2.1),
       (5, 2, {d '2020-05-01'}, 2.2),
       (6, 3, {d '2020-06-01'}, 2.3),
       (7, 1, {d '2020-07-01'}, 3.1),
       (8, 2, {d '2020-08-01'}, 3.2),
       (9, 3, {d '2020-09-01'}, 3.3),
       (10, 1, {d '2020-10-01'}, 4.1),
       (11, 2, {d '2020-11-01'}, 4.2),
       (12, 3, {d '2020-12-01'}, 4.3)

I'd like to get the Value per SubID where PointInTime is the maximum value (over SubID).

The only way I can think of right now is a self join, something like this:

SELECT A.SubID, A.Value 
FROM tblDummy AS A
INNER JOIN 
    (SELECT SubID, MAX(PointInTime) AS PIT 
     FROM tblDummy 
     GROUP BY SubID) AS B ON A.SubID = B.SubID AND A.PointInTime = B.PIT

But I don't like the sub query and I don't like that the join uses columns that are not unique. So, if the data contains a "PointInTime" value twice (with the same "SubID"), then data doubles because of the join.

Is there any other way to reach my goal? Maybe with the PARTITION ... OVER syntax?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want window functions:

select d.*
from (select d.*,
             row_number() over (partition by subid order by PointInTime desc) as seqnum
      from tblDummy d
     ) d
where seqnum = 1;

You can also express this using a correlated subquery:

select d.*
from tblDummy d
where d.PointInTime = (select max(d2.PointInTime)
                       from tblDummy d2
                       where d2.subid = d.subid
                      );

Both these queries can benefit from an index on tblDummy(subid, PointInTime).

Upvotes: 1

Related Questions