How to select max value from n number linked tuples

Question

I'm trying to select the max id from any n number linked tuples in a sql server db - we are writing an upgrade script for some data sets within an app, and need to know what the highest upgrade available is based on what the data's version is currently. For example, using the following simplified table 'versions':

oldVersionId   newVersionId
1              2
2              3
3              4
10             11

We know we are version 1, and want to get the highest version out there that we can upgrade to; which would return 4 in this case, and not 11. We can have 0-n number of upgradable versions available at any given time. I'm not an sql wiz, and could only think to query using a variable number of chained selects:

select newVersion from versions where oldVersionId = (select newVersion from versions where oldVersionId = 1)

But it not an n numbered search, and won't return correctly if the number of elements is greater or less then the given. Is sql capable of performing such a query, and what elements / keywords should I be looking at to write one?

Solution:

You learn something new every day - I needed a hybrid of the two answers. Turns out sql can query a dataset using the tree-like child/parent linking that I'm way more comfortable with in OO languages.

In sql server you can set up a recursive tree walking call using a aliased table. You need an anchor and then the recursive bit. The first call is the anchor, I can use any value in the table, or a list of values, etc. The second select call just says to use the rest of the table to scan against.

Here is the syntax:

--Create the new alias (s)
;with s (oldVersionId, newVersionId) as 
(
    --set up the anchor node, 
    select oldVersionId, newVersionId from @t
    where oldVersionId = 1

    -- join it to the rest of the table, denoting that we only want nodes 
    -- where the old version is represented as a new version later
    union all
    select t.oldVersionId, t.newVersionId from @t as t
    inner join s on t.oldVersionId = s.newVersionId
)
--Return the max value from the nodes I collected
select max(s.newVersionId) from s

Good question - no, it should be 4. There is no way to link 1 to 10 using any values in the table. We can link 1 to 4 through 2 and 3, so 4 is correct. — Noah, Oct 13 '11 at 18:47

sarh · Answer 1 · 2011-10-13T20:00:16.713

Here is the solution with CTE - loop thru data while we have next match on oldVersionId = newVersionId:

declare @t table (
    oldVersionId int,
    newVersionId int )

insert into @t values (1,2)
insert into @t values (2,3)
insert into @t values (3,4)
insert into @t values (10,11)
insert into @t values (11,12)
insert into @t values (14,15)

declare @startVer int
set @startVer = 1

;with s (oldVersionId, newVersionId) as 
(
    select top 1 oldVersionId, newVersionId from @t
    where oldVersionId = @startVer
    union all
    select t.oldVersionId, t.newVersionId from @t as t
    inner join s on t.oldVersionId = s.newVersionId
)
select max(s.newVersionId) 
from s
option (maxrecursion 0)

And here is solution without CTE - search for the last record which has newVersionId equals to 1 (1st version) plus the sum of imcremental updates to this version:

select max(t1.newVersionId)
from @t t1
where t1.oldVersionId >= @startVer
and t1.newVersionId = @startVer + (
    select sum(newVersionId - oldVersionId) 
    from @t 
    where 
        oldVersionId >= @startVer and 
        oldVersionId < t1.newVersionId)

This looks very close, but I am unable to provide the oldVersionId to use; If I were to upgrade from version 10, I need the result to be 11. I'm playing with the syntax to see if I can't figure it out. — Noah, Oct 13 '11 at 19:46
Ok, I've updated the code - added startVer parameter and more sample rows. If you will set @startVer = 10, then you will get 12 — sarh, Oct 13 '11 at 20:01

score 0 · Accepted Answer · answered Oct 13 '11 at 19:04

0

This problem can be solved by writing recursive queries to traverse recursive hierarchies in a table.

http://www.mssqltips.com/sqlservertip/1520/recursive-queries-using-common-table-expressions-cte-in-sql-server/

http://msdn.microsoft.com/en-us/library/ms186243.aspx

answered Oct 13 '11 at 19:04

Bala

4,427
6
26
29

Wow, something I never know SQl could do, thank you very much. I'm playing with it trying to get it to work with the example I gave. – Noah Oct 13 '11 at 19:47

score 0 · Answer 3 · answered Oct 13 '11 at 19:06

You effectively need to identify the final node in a linked list--seems to me your best bet would be to use the recursive features of CTEs to get to your 'max' version, but I'm not familiar enough with CTEs to get it working.

The following gets to the right answer, but only because I know how many links this particular dummy table will require beforehand; thus, not ideal.

CREATE TABLE #temp (
oldversionID SMALLINT,
newversionID SMALLINT )

INSERT INTO #temp
VALUES (1,2)
INSERT INTO #temp
VALUES (2,3)
INSERT INTO #temp
VALUES (3,4)
INSERT INTO #temp
VALUES (10,11);


select t1.oldversionID, t3.newversionID from #temp t1
inner join #temp t2
on t1.newversionId = t2.oldversionID
inner join #temp t3
on t2.newversionId = t3.oldversionID

score 0 · Answer 4 · edited May 23 '17 at 11:55

0

Your problem is pretty much same as How to get the parent given a child in SQL SERVER 2005

I think same CTE will work for you.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 13 '11 at 19:18

Bala

4,427
6
26
29

How to select max value from n number linked tuples

4 Answers4