Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL dilemma Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 5:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:39 AM
Points: 268, Visits: 196
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?
Post #1567741
Posted Tuesday, May 6, 2014 12:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 13,606, Visits: 10,490
Your question is not really clear. Can you post the table DDL, sample data and desired output?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1567786
Posted Tuesday, May 6, 2014 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 7,094, Visits: 6,912
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 [table])
SELECT MAX(C)
FROM [table]
WHERE [table].B = cte.B




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

Anon.

Post #1567860
Posted Tuesday, May 6, 2014 5:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 7,208, Visits: 13,669
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1567878
Posted Tuesday, May 6, 2014 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:39 AM
Points: 268, Visits: 196
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.
Post #1568048
Posted Tuesday, May 6, 2014 9:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 7,094, Visits: 6,912
;WITH d (A, B, RowNo) AS (
SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC)
FROM [table])
SELECT t.A, t.B, MAX(t.C) AS [C]
FROM d
JOIN [table] 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.

Post #1568052
Posted Tuesday, May 6, 2014 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 7,094, Visits: 6,912
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 [table])
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.

Post #1568059
Posted Wednesday, May 7, 2014 1:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 7,208, Visits: 13,669
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1568311
Posted Wednesday, May 7, 2014 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 7,094, Visits: 6,912
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.

Post #1568403
Posted Wednesday, May 7, 2014 9:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:39 AM
Points: 268, Visits: 196
Thank you.
Post #1568537
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse