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

Question

Post reply

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

drew.allen

SSC Guru

Points: 76944

April 11, 2024 at 7:56 pm

Answer

#4410261

This produces the exact same results as Jonathan's query with far fewer reads.

WITH CTE AS
(
    SELECT xp.RBDTYR
         , xp.RBDCTL
         , xp.RBDTTC
         , ROW_NUMBER() OVER(PARTITION BY xp.RBDCTL, xp.RBDTTC ORDER BY xp.RBDTYR DESC) AS rn
    FROM #XXXRE60P AS xp
)
SELECT CTE.RBDTYR
     , CTE.RBDCTL
     , CTE.RBDTTC
FROM CTE
WHERE rn = 1;

Here are the read stats

/*  Jonathan's Query  */
Table '#XXXRE60P_00000000322B'. Scan count 16, logical reads 16, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

/*  Drew's Query  */
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#XXXRE60P_00000000322B'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 15 posts - 16 through 30 (of 31 total)

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

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 1

Jonathan AC Roberts wrote:

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:

WOW! Genius. Well done. I'm feeling more old and stupid than normal right now.

"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 2

DesNorton wrote:

OK, try it as a basic aggregate

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

OK, so far, so good... I've modified/expanded the code to pull in a bit more information...

SELECT TOP (100) PERCENT RBDDSN, RBDCTL, RBDTTC, MAX(RBDTYR) AS MaxYear
FROM INFOCON.S1029086.IC1ASLIB.XXXRE60P AS CurrentTaxes
GROUP BY CurrentTaxes.RBDDSN, CurrentTaxes.RBDCTL, CurrentTaxes.RBDTTC
HAVING (RBDTTC = 300)
ORDER BY RBDCTL

Which returns...

Result1

BUT, as soon as I try to pull in other columns (tax amounts) associated with RBDCTL, I start getting 2022 records?

SELECT TOP (100) PERCENT RBDDSN, RBDCTL, RBDTTC, MAX(RBDTYR) AS MaxYear, RBDDSA, RBDFCA, RBDPNA
FROM INFOCON.S1029086.IC1ASLIB.XXXRE60P AS CurrentTaxes
GROUP BY CurrentTaxes.RBDDSN, CurrentTaxes.RBDCTL, CurrentTaxes.RBDTTC, CurrentTaxes.RBDDSA, CurrentTaxes.RBDFCA, CurrentTaxes.RBDPNA
HAVING (RBDTTC = 300)
ORDER BY RBDCTL

Result2

I apologize for asking such noob questions, but this kinda got dumped on me, and I'm stumped.

I also apologize for not including ALL needed columns from the beginning, I wanted to keep my question as simple as possible.

This reply was modified 2 weeks, 6 days ago by tsobiech.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Have you tried my solution?

;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
;

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

Jonathan AC Roberts wrote:

Have you tried my solution?

;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
;

I did, and it returned results... However I'm too embarrassed to admit that I don't know if it's what I need because when I paste the code into a View, it cannot display graphically, so therefore I don't know where to add filter RBDTTC = 300 and I don't know how to sort it by RBDDSN, RBDCTL to verify.

I need to be able to re-use the code in separate views, each with a different RBDTTC. 100, 200, 300, etc.

This reply was modified 2 weeks, 6 days ago by tsobiech.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 5

tsobiech wrote:

Jonathan AC Roberts wrote:
Have you tried my solution?
;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
;
I did, and it returned results... However I'm too embarrassed to admit that I don't know if it's what I need because when I paste the code into a View, it cannot display graphically, so therefore I don't know where to add filter RBDTTC = 300 and I don't know how to sort it by RBDDSN, RBDCTL to verify.
I need to be able to re-use the code in separate views, each with a different RBDTTC. 100, 200, 300, etc.

;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
  WHERE b.RBDTTC = 300
  ORDER BY b.RBDDSN, b.RBDCTL;

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

Jonathan AC Roberts wrote:

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:

Any chance you could do that with this table, please?

(This is a full sample of my raw data)

XXXRE60PRawData

Chat GPT 3.5 can't do it.

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

;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
  WHERE b.RBDTTC = 300
  ORDER BY b.RBDDSN, b.RBDCTL;

This works on my test table, but when I replace my test table (XXXRE60P) with the "live data" table in my linked database (INFOCON.S1029086.IC1ASLIB.XXXRE60P) it times out. I give up.

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

This reply was modified 2 weeks, 6 days ago by tsobiech.

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

tsobiech wrote:

Jonathan AC Roberts wrote:
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:
Any chance you could do that with this table, please? (This is a full sample of my raw data)
Chat GPT 3.5 can't do it.

CREATE TABLE XXXRE60P (
  RBDDSN INT,
  RBDDCTL INT,
  RBDDTIC INT,
  RBDDTYR INT,
  RBDDSA DECIMAL(10, 2),
  RBDDFCA DECIMAL(10, 2),
  RBDDPNA DECIMAL(10, 2)
);

INSERT INTO XXXRE60P (RBDDSN, RBDDCTL, RBDDTIC, RBDDTYR, RBDDSA, RBDDFCA, RBDDPNA) VALUES
(1, 1, 100, 2024, 992.12, 1012.37, 1022.99),
(1, 1, 100, 2023, 918.08, 936.82, 983.66),
(1, 1, 200, 2022, 28.14, 28.71, 30.15),
(1, 1, 300, 2023, 2058.28, 2107.29, 2310.31),
(1, 1, 300, 2022, 1926.80, 1980.00, 2017.18),
(1, 3, 100, 2023, 699.28, 713.55, 749.23),
(1, 3, 100, 2022, 647.09, 660.30, 693.32),
(1, 3, 200, 2024, 19.84, 20.24, 21.25),
(1, 3, 200, 2023, 19.84, 20.24, 21.25),
(1, 3, 200, 2022, 19.84, 20.24, 21.25),
(1, 3, 300, 2023, 1450.74, 1484.30, 1526.27),
(1, 3, 300, 2022, 1393.34, 1421.78, 1421.78),
(1, 4, 100, 2024, 1709.13, 1744.01, 1831.21),
(1, 4, 100, 2023, 1581.58, 1613.86, 1694.55),
(1, 4, 100, 2022, 1581.58, 1613.86, 1694.55),
(1, 4, 200, 2024, 48.47, 49.46, 51.93),
(1, 4, 200, 2023, 48.47, 49.46, 51.93),
(1, 4, 200, 2022, 48.47, 49.46, 51.93),
(1, 4, 300, 2023, 3545.80, 3618.17, 3979.98),
(1, 4, 300, 2022, 3405.51, 3475.00, 3475.00),
(1, 5, 100, 2024, 296.78, 304.89, 318.76),
(1, 5, 100, 2023, 274.64, 280.24, 294.25),
(1, 5, 100, 2022, 274.64, 280.24, 294.25),
(1, 5, 200, 2024, 8.42, 8.59, 9.02),
(1, 5, 200, 2023, 8.42, 8.59, 9.02),
(1, 5, 200, 2022, 8.42, 8.59, 9.02),
(1, 5, 300, 2023, 615.71, 628.29, 661.09),
(1, 5, 300, 2022, 591.35, 603.42, 603.42);

ChatGPT can make mistakes. Consider checking important information.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 9

tsobiech wrote:

;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
  WHERE b.RBDTTC = 300
  ORDER BY b.RBDDSN, b.RBDCTL;

This works on my test table, but when I replace my test table (XXXRE60P) with the "live data" table in my linked database (INFOCON.S1029086.IC1ASLIB.XXXRE60P) it times out. I give up.

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

Try this, it is possibly faster:

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

How many rows are on the prod table?

Is it possible for you to add an index to the table?

I know of a very fast method of querying the values you want but you need to add an index to the table.

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

How many rows are on the prod table?

187,450

Is it possible for you to add an index to the table?

I don't believe so, as I only have read-only access to the linked database.

I know of a very fast method of querying the values you want but you need to add an index to the table.

I really appreciate all of your help.

All of this got dumped on me to "figure out," because I'm the ONLY one who has even basic knowledge of SQL.

I'm about ready to quit my job over this... seriously.

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

Try this, it is possibly faster:

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

That also timed out, but thanks again.

At this point, I've accepted that I probably will just have to manually declare the RBDTTC (code) and RBDTYR (year) that I need, each time I need to update.

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 12

tsobiech wrote:

Jonathan AC Roberts wrote:
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:
Any chance you could do that with this table, please? (This is a full sample of my raw data)
Chat GPT 3.5 can't do it.

Consider learning how to do it without CHATGPT. See the article that you were previously directed to. It's also at the first link in my signature line below. It is an OLD article and probably needs to be updated to use Table Valued Constructors (VALUES) but it still gets the job done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

drew.allen wrote:

This produces the exact same results as Jonathan's query with far fewer reads.

WITH CTE AS
(
    SELECT xp.RBDTYR
         , xp.RBDCTL
         , xp.RBDTTC
         , ROW_NUMBER() OVER(PARTITION BY xp.RBDCTL, xp.RBDTTC ORDER BY xp.RBDTYR DESC) AS rn
    FROM #XXXRE60P AS xp
)
SELECT CTE.RBDTYR
     , CTE.RBDCTL
     , CTE.RBDTTC
FROM CTE
WHERE rn = 1;

Drew

This seems to have worked perfectly! THANKS!

I wish I understood EXACTLY how this worked, but I'm guessing the ORDER BY DESC for RDTYR, then selecting ROW_NUMBER 1 is the substitute for "MAX"

Just an FYI, I was able to add more columns (see raw data table) and select RBDTTC = 300 ( I will also use this same code for 100, 200, etc)

WITH CTE AS
(
    SELECT xp.RBDTYR
         , xp.RBDDSN
         , xp.RBDCTL
         , xp.RBDTTC
         , xp.RBDDSA
         , xp.RBDFCA
         , xp.RBDPNA
         , ROW_NUMBER() OVER(PARTITION BY xp.RBDCTL, xp.RBDTTC ORDER BY xp.RBDTYR DESC) AS rn
    FROM INFOCON.S1029086.IC1ASLIB.XXXRE60P AS xp
)
SELECT CTE.RBDTYR
     , CTE.RBDDSN
     , CTE.RBDCTL
     , CTE.RBDTTC
     , CTE.RBDDSA
     , CTE.RBDFCA
     , CTE.RBDPNA

FROM CTE
WHERE rn = 1 AND RBDTTC = 300;

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 14

drew.allen wrote:

This produces the exact same results as Jonathan's query with far fewer reads.

WITH CTE AS
(
    SELECT xp.RBDTYR
         , xp.RBDCTL
         , xp.RBDTTC
         , ROW_NUMBER() OVER(PARTITION BY xp.RBDCTL, xp.RBDTTC ORDER BY xp.RBDTYR DESC) AS rn
    FROM #XXXRE60P AS xp
)
SELECT CTE.RBDTYR
     , CTE.RBDCTL
     , CTE.RBDTTC
FROM CTE
WHERE rn = 1;

Here are the read stats

/*  Jonathan's Query  */
Table '#XXXRE60P_00000000322B'. Scan count 16, logical reads 16, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

/*  Drew's Query  */
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table '#XXXRE60P_00000000322B'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Drew

Nice one. I'm just thinking now I might have some code I'll need to update to use this method.