TSQL dilemma

Question

TSQL dilemma

fizzleme

SSCertifiable

Points: 6906
More actions
May 5, 2014 at 5:19 pm

#307130

I have a table with an identifying field that is duplicated. I want to get a single record for this field by referencing 2 other fields. A max command will locate one of the fields but the second is based on the first. messy. Here is the concept
Select A from table where (B = select max(B) from table) gets me part of the way there.
The problem is i need the maximum value for C based on A and the maximum value for B.
Is there a TSQL genius who can help?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Your question is not really clear. Can you post the table DDL, sample data and desired output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

David Burrows SSC Guru Points: 65138 More actions · Answer 2

Koen Verbeeck (5/6/2014)
Your question is not really clear. Can you post the table DDL, sample data and desired output?

+1

The only thing I can deduce is this

;WITH cte (B)

AS (SELECT MAX(B) FROM

)

SELECT MAX(C)

FROM

WHERE

.B = cte.B

Far away is close at hand in the images of elsewhere.
Anon.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

It won't need a genius, only a decent explanation. Perhaps a model would help. Try tinkering with this for starters:

SELECT [identifying field], A, B, C,

MAX(B) OVER(PARTITION BY [identifying field]),

CASE WHEN B = MAX(B) OVER(PARTITION BY [identifying field]) THEN 'YES' ELSE 'NO' END

FROM (

SELECT 100, 21, 1, 40 UNION ALL

SELECT 100, 22, 2, 50 UNION ALL

SELECT 100, 23, 3, 60 UNION ALL

SELECT 101, 41, 1, 10 UNION ALL

SELECT 101, 42, 2, 20 UNION ALL

SELECT 101, 43, 3, 30

) d ([identifying field], A, B, C)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

fizzleme SSCertifiable Points: 6906 More actions · Answer 4

It is hard to explain.

Imagine records like this

a b c

12345 10000 1

12345 10000 2

12345 10000 3

12345 10001 1

12346 10001 1

12347 20000 1

I reference a and obtain the maximum value for b and c for a given a. when multiple b's exist for an a

c is incremented for each b.

so I want the 4th 5th and 6th records. does this help? If not I understand.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.

David Burrows SSC Guru Points: 65138 More actions · Answer 5

;WITH d (A, B, RowNo) AS (

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)

FROM

)

SELECT t.A, t.B, MAX(t.C) AS [C]

FROM d

JOIN

t ON t.A = d.A AND t.B = d.B

WHERE d.RowNo = 1

GROUP BY t.A, t.B

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65138 More actions · Answer 6

Or variation on Chris'

;WITH d (A,B,C,MAXB,MAXC) AS (

SELECT A,B,C,

MAX(B) OVER(PARTITION BY A),

MAX(C) OVER(PARTITION BY A,B)

FROM

)

SELECT A,B,C

FROM d

WHERE B = MAXB

AND C = MAXC

*Edited* To fix

Far away is close at hand in the images of elsewhere.
Anon.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Variation on David's;

DROP TABLE #Sample;

CREATE TABLE #Sample (a INT, b INT, c INT);

INSERT INTO #Sample (a, b, c)

SELECT * FROM (VALUES (12345, 10000, 1),

(12345, 10000, 2),

(12345, 10000, 3),

(12345, 10001, 1),

(12346, 10001, 1),

(12347, 20000, 1)) d (a, b, c);

WITH Selector AS (

SELECT

a, b, c, rn = ROW_NUMBER() OVER(PARTITION BY a ORDER BY b DESC, c DESC)

FROM #Sample

)

SELECT a, b, c

FROM Selector

WHERE rn = 1;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Burrows SSC Guru Points: 65138 More actions · Answer 8

ChrisM@Work (5/7/2014)
Variation on David's;
DROP TABLE #Sample;
CREATE TABLE #Sample (a INT, b INT, c INT);
INSERT INTO #Sample (a, b, c)
SELECT * FROM (VALUES (12345, 10000, 1),
(12345, 10000, 2),
(12345, 10000, 3),
(12345, 10001, 1),
(12346, 10001, 1),
(12347, 20000, 1)) d (a, b, c);
WITH Selector AS (
SELECT
a, b, c, rn = ROW_NUMBER() OVER(PARTITION BY a ORDER BY b DESC, c DESC)
FROM #Sample
)
SELECT a, b, c
FROM Selector
WHERE rn = 1;

Nice 🙂

Far away is close at hand in the images of elsewhere.
Anon.

fizzleme SSCertifiable Points: 6906 More actions · Answer 9

Thank you.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.