Selecting MAX value in 1 column based on code in another column?

Question

Selecting MAX value in 1 column based on code in another column?

tsobiech

SSC Eights!

Points: 815
More actions
April 11, 2024 at 12:25 pm
Go to Answer
#4410079
Here is a simplified sample of my data... (there are additional columns, but not relevant right now.)
What I need is a SELECT statement that will always return the MAX "RBDTYR" (year) for each RBDTTC, for each RBDCTL
Here's what I have, so far...
```
SELECT RBDTYR, RBDCTL, RBDTTC
FROM XXXRE60P AS XXXRE60P_1
WHERE (RBDTYR =
(SELECT MAX(RBDTYR) AS Expr1
FROM XXXRE60P AS XXXRE60P_1)) AND (RBDTTC = 100)
```
Which returns (good, so far)...
This WORKS for RBDTTC = 100, and RBDTTC = 200, because all instances of those codes have been updated to 2024.
However, it returns NO records if RBDTTC = 300 because there are no "2024" records for that code.
What I need are these records for RBDTTC = 300...
I could always just manually specify the RBDTYR and RBDTTC...
```
SELECT TOP RBDTYR, RBDCTL, RBDTTC
FROM XXXRE60P AS XXXRE60P_1
WHERE (RBDTYR = 2023) AND (RBDTTC = 300)
```
Which returns basically what I need...

However, I'd rather not have to manually update the "RBDTYR" value every year.
How do I SELECT in such a way that it will return the MAX value RBDTYR row for each RBDTTC and RBDCTL?
- This topic was modified 7 months, 2 weeks ago by tsobiech.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

Try using a window function

SELECT    RBDCTL
        , RBDTTC
        , MaxYear = MAX( RBDTYR ) OVER ( PARTITION BY RBDTTC, RBDCTL )
FROM      XXXRE60P
-- WHERE ...
GROUP BY  RBDTTC, RBDCTL;

How to post data/code on a forum to get the best help.

drew.allen SSC Guru Points: 76985 More actions · Answer 2

Please create sample data in a consumable format using the {;} Code button to include a script to create a TEMP table and insert data into that temp table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

tsobiech SSC Eights! Points: 815 More actions · Answer 3

DesNorton wrote:

Try using a window function

SELECT    RBDCTL
        , RBDTTC
        , MaxYear = MAX( RBDTYR ) OVER ( PARTITION BY RBDTTC, RBDCTL )
FROM      XXXRE60P
-- WHERE ...
GROUP BY  RBDTTC, RBDCTL;

That returns the following error...

Column 'XXXRE60P.RBDTYR' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add RBDTYR to the GROUP BY clause, then the results aren't what I need.

I'm using MSSMS and querying a linked server... if that makes a difference.

This reply was modified 7 months, 2 weeks ago by tsobiech.

tsobiech SSC Eights! Points: 815 More actions · Answer 4

drew.allen wrote:

Please create sample data in a consumable format using the {;} Code button to include a script to create a TEMP table and insert data into that temp table.
Drew

I have absolutely no idea what you mean.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

tsobiech wrote:

drew.allen wrote:
Please create sample data in a consumable format using the {;} Code button to include a script to create a TEMP table and insert data into that temp table.
Drew
I have absolutely no idea what you mean.

Basically means, if you want us to write a query based on your data & structure, in order for us to test it out, we need a script that generates your data & structure. Otherwise we have to build out our own data & structure, then test a query for you. We're just asking to meet half way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

tsobiech SSC Eights! Points: 815 More actions · Answer 6

we need a script that generates your data & structure.

...and how do I do that? I don't mind taking the time, I just don't even know what step 1 is.

This reply was modified 7 months, 2 weeks ago by tsobiech.

DesNorton SSC-Insane Points: 24386 More actions · Answer 7

OK, try it as a basic aggregate

SELECT    RBDCTL
        , RBDTTC
        , MaxYear = MAX( RBDTYR )
FROM      XXXRE60P
-- WHERE ...
GROUP BY  RBDTTC, RBDCTL;

How to post data/code on a forum to get the best help.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

;WITH CTE AS
(
    SELECT DISTINCT
           RBDCTL,
           RBDTTC
      FROM XXXRE60P
)
SELECT b.*
  FROM CTE a
 CROSS APPLY(SELECT TOP(1) *
               FROM XXXRE60P b
              WHERE b.RBDCTL = a.RBDCTL
                AND b.RBDTTC = a.RBDTTC
              ORDER BY RBDTYR DESC) b
;

DesNorton SSC-Insane Points: 24386 More actions · Answer 9

To create sample data for people to assist you with ...

CREATE TABLE #TsetData (RBDCTL int, RBDTTC int, RBDTYR  int);

INSERT INTO #TsetData ( RBDCTL, RBDTTC, RBDTYR )
VALUES ( 1, 300, 2022 )
     , ( 1, 20, 2023 )
     , ... etc

How to post data/code on a forum to get the best help.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

tsobiech wrote:

we need a script that generates your data & structure.
...and how do I do that? I don't mind taking the time, I just don't even know what step 1 is.

Fair enough. Here's an article that walks you through various mechanisms to get that done. It's just about getting you valid and accurate help. Without your structures and data, we might make bad assumptions or poor choices. Worst of all, we might just be guessing. Actual structures and actual data along with expected results make it much easier to help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 11

I uploaded your picture to ChatGPT 4 and asked it to: "Write a SQL Server create table (table name: XXXRE60P) and insert script for this, make sure you include all rows to insert in your answer." and it produced this:

CREATE TABLE XXXRE60P (
    RBDTYR INT,
    RBDCTL INT,
    RBDTTC INT
);

INSERT INTO XXXRE60P (RBDTYR, RBDCTL, RBDTTC) VALUES 
(2022, 1, 300),
(2023, 1, 100),
(2023, 1, 200),
(2023, 1, 300),
(2024, 1, 100),
(2024, 1, 200),
(2022, 1, 100),
(2022, 1, 200),
(2022, 3, 100),
(2022, 3, 200),
(2024, 3, 100),
(2024, 3, 200),
(2023, 3, 300),
(2023, 3, 100),
(2023, 3, 200),
(2022, 3, 300),
(2022, 4, 300),
(2023, 4, 100),
(2023, 4, 200),
(2023, 4, 100),
(2024, 4, 100),
(2024, 4, 200),
(2022, 4, 100),
(2022, 4, 200),
(2022, 5, 100),
(2022, 5, 200),
(2024, 5, 100),
(2024, 5, 200),
(2023, 5, 300),
(2023, 5, 100),
(2023, 5, 200),
(2022, 5, 300),
(2022, 6, 300),
(2023, 6, 100),
(2023, 6, 200),
(2023, 6, 300),
(2024, 6, 100),
(2024, 6, 200),
(2024, 6, 300),
(2022, 6, 100),
(2022, 6, 200);

tsobiech SSC Eights! Points: 815 More actions · Answer 12

DesNorton wrote:

OK, try it as a basic aggregate

SELECT    RBDCTL
        , RBDTTC
        , MaxYear = MAX( RBDTYR )
FROM      XXXRE60P
-- WHERE ...
GROUP BY  RBDTTC, RBDCTL;

This works on tables in my source database, but if I try similar code on a linked database,

SELECT RBDCTL, RBDTTC, MAX(RBDTYR) AS MaxYear
FROM INFOCON.S1029086.IC1ASLIB.XXXRE60P AS XXXRE60P_1
GROUP BY XXXRE60P.RBDTTC, XXXRE60P.RBDCTL

It fails with...

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "XXXRE60P.RBDTTC" could not be bound.

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "XXXRE60P.RBDCTL" could not be bound.

Any idea why?

This reply was modified 7 months, 2 weeks ago by tsobiech.
This reply was modified 7 months, 2 weeks ago by tsobiech.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 13

tsobiech wrote:

DesNorton wrote:
OK, try it as a basic aggregate
SELECT    RBDCTL
        , RBDTTC
        , MaxYear = MAX( RBDTYR )
FROM      XXXRE60P
-- WHERE ...
GROUP BY  RBDTTC, RBDCTL;
This works on tables in my source database, but if I try similar code on a linked database...
This works on tables in my source database, but if I try similar code on a linked database...


SELECT RBDCTL, RBDTTC, MAX(RBDTYR) AS MaxYear
FROM INFOCON.S1029086.IC1ASLIB.XXXRE60P AS XXXRE60P_1
GROUP BY XXXRE60P.RBDTTC, XXXRE60P.RBDCTL
It fails with... Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "XXXRE60P.RBDTTC" could not be bound. Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "XXXRE60P.RBDCTL" could not be bound.
Any idea why?

You forgot the "_1" in the group by:

GROUP BY XXXRE60P_1.RBDTTC, XXXRE60P_1.RBDCTL

tsobiech SSC Eights! Points: 815 More actions · Answer 14

You forgot the "_1" in the group by: GROUP BY XXXRE60P_1.RBDTTC, XXXRE60P_1.RBDCTL

Ugh, such a stupid thing to overlook. I don't do this every day so... my apologies.

This reply was modified 7 months, 2 weeks ago by tsobiech.