ANSI Join with a MAX recursive select

Question

ANSI Join with a MAX recursive select

Josh Hollandsworth

SSC Enthusiast

Points: 136
More actions
December 21, 2006 at 8:39 am

#116739

We (my company) are looking to upgrade to 2k5 and when we do, we are trying to make all of our joins use ANSI Join Syntax. There are many places where we use a MAX recursive select. I was wondering if any one has done this ANSI Style. Could some one tell me how far off base I am with the following code
SELECT
TBL1.COL1
TBL2.COL2
FROM
TBL1
INNER JOIN (TBL2
INNER JOIN TBL2 AS TBL3 ON TBL2.COL3 = MAX(TBL3.COL3)
&nbsp ON TBL1.COL1 = TBL2.COL1

Instead of
SELECT
TBL1.COL1
TBL2.COL2
FROM
TBL1, TBL2
WHERE
TBL1.COL1 = TBL2.COL1
AND TBL2.COL3 = (SELECT MAX(COL3) FROM TBL2 WHERE COL1 = TBL2.COL1)

Viewing 5 posts - 1 through 4 (of 4 total)

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hi Josh,

please could you put the logic into words? I'm not sure what precisely you are trying to do ... here is one way how to understand and interpret it using JOIN syntax - but maybe I'm completely off the mark and you need to do it differently.

SELECT TBL1.COL1, a.COL2

FROM TBL1

INNER JOIN TBL2 a ON a.COL1=TBL1.COL1

INNER JOIN

(

SELECT TBL2.COL1, MAX(TBL2.COL3) AS maxval

FROM TBL2

GROUP BY TBL2.COL1

) AS Q ON a.COL1=Q.COL1 AND a.COL3=Q.maxval

Josh Hollandsworth SSC Enthusiast Points: 136 More actions · Answer 2

OK the real scenario is we are managing plant records for a telco, any time some one signs up, disconnects or has to be changed to new record, we insert a new record with an incremented sequence number

So if we have multiple rows that have simlar info except for a sequence number

IE

So we could have something like

'A' 'B' 'C' CUST_1 SEQ1

'A' 'B' 'C' CUST_2 SEQ2

'D' 'E' 'F' CUST_5 SEQ1

'D' 'E' 'F' CUST_1 SEQ2

'D' 'E' 'F' CUST_6 SEQ3

'D' 'E' 'F' CUST_6 SEQ4

I would only want the record that has the highest sequence number

The result set should be

'A' 'B' 'C' CUST_2 SEQ2

'D' 'E' 'F' CUST_6 SEQ4

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 3

--Just to get your example data in a table --Start

CREATE TABLE #A

(A Varchar (1)

,B Varchar (1)

,C Varchar (1)

,D Varchar (20))

INSERT INTO #A

(A, B, C, D)

VALUES('A', 'B', 'C', 'CUST_1 SEQ1')

INSERT INTO #A

(A, B, C, D)

VALUES('A', 'B', 'C', 'CUST_2 SEQ2')

INSERT INTO #A

(A, B, C, D)

VALUES('D', 'E', 'F', 'CUST_5 SEQ1')

INSERT INTO #A

(A, B, C, D)

VALUES('D', 'E', 'F', 'CUST_1 SEQ2')

INSERT INTO #A

(A, B, C, D)

VALUES('D', 'E', 'F', 'CUST_6 SEQ3')

INSERT INTO #A

(A, B, C, D)

VALUES('D', 'E', 'F', 'CUST_6 SEQ4')

--End Example data insert

--Select statement to pull data

SELECT A, B, C, MAX(D)

FROM #A

GROUP BY A, B, C

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Hi Josh,

If the sequence number really contains customer, and "highest sequence number" takes into account the entire string (custome+seq.no.) then it is as easy as JDixon posted - but I suspect that this is not what you meant. Unfortunately it isn't clear from what you posted (it is always best to post DDL of the table), but it seems to me that this could be closer to your requirements:

/*Prepare test environment*/

CREATE TABLE #connlog (Col1 VARCHAR (1), Col2 VARCHAR (1), Col3 VARCHAR (1), Customer VARCHAR(20), Seq_no INT)