How to get value of deepest layer

Question

From table TX001a below, how do I get the value of column hzdepb_r (bottom layer) from the last row of each cokey?

I'd like to get the hzdepb_r values as follows:
Row 5: 203
Row 9: 203
Row 11: 213
Row 15: 203

Then insert these into table TX001b's zmx column.

Here are the sample tables:

IF OBJECT_ID('dbo.TX001a', 'U') IS NOT NULL DROP TABLE dbo.TX001a;
CREATE TABLE dbo.TX001a(
  cokey [varchar] (30) NULL,
  hzdept_r [smallint] NULL,
  hzdepb_r [smallint] NULL);

IF OBJECT_ID('dbo.TX001b', 'U') IS NOT NULL DROP TABLE dbo.TX001b;
CREATE TABLE dbo.TX001b(
  cokey [varchar] (30) NULL,
  zmx [smallint] NULL);

INSERT INTO dbo.TX001a(cokey, hzdept_r, hzdepb_r)
VALUES
('57114:842758', 0, 20),
('57114:842758', 20, 46),
('57114:842758', 46, 81),
('57114:842758', 81, 173),
('57114:842758', 173, 203),
('57115:842759', 0, 15),
('57115:842759', 15, 81),
('57115:842759', 81, 173),
('57115:842759', 173, 203),
('57116:842760', 0, 20),
('57116:842760', 20, 213),
('57117:842761', 0, 15),
('57117:842761', 15, 76),
('57117:842761', 76, 168),
('57117:842761', 168, 203);

Thanks so much in advance.

---

Marilyn Gambone

Answer 1

The first what comes into mind is a recursive CTE, e.g.

WITH	Hierarchy AS 
	(
		SELECT	cokey ,
				0 AS [level] ,
				hzdepb_r
		FROM	tx001a
		WHERE	hzdept_r = 0
		UNION ALL
		SELECT	C.cokey ,
				[level] + 1 ,
				C.hzdepb_r
		FROM	Hierarchy P
				INNER JOIN tx001a C ON C.cokey = P.cokey
									AND C.hzdept_r = P.hzdepb_r
	)
	SELECT	*
	FROM	Hierarchy O
	WHERE	O.[level] = 
		(
			SELECT	MAX(I.[level])
			FROM	Hierarchy I
			WHERE	I.cokey = O.cokey
		);

But I'm not sure whether this is really necessary, as your data structure also gives you this possibility:

SELECT	cokey ,
		MAX(hzdepb_r)
FROM	tx001a
GROUP BY cokey;

Answer 2

Hi,

You're right.  I could just use the MAX function. Here's what worked:

WITH UPD AS (SELECT DISTINCT cokey, MAX(hzdepb_r) OVER (PARTITION BY cokey) AS C
FROM tx001a)
UPDATE b
SET b.zmx = a.C
FROM tx001b b INNER JOIN UPD a
ON b.cokey = a.cokey;

Thanks for pointing this out; helped a lot.

---

Marilyn Gambone