Select state with longest borders-CodePudding

I want to find the name of the state that shares the longest border with one or more other state? I have two table state, and borders. The table provided are simplified, to only have 5 states

"state" Code is our key that is unquie for each state

name	code
Michigan	MI
Indiana	IN
Illinois	IL
Ohio	OH
Wisconsin	WI

"borders" two state codes to refer to border between two states

code1	code2	length
MI	IN	20
IL	IN	50
Mi	OH	5
IN	OH	40
WI	MI	30

Expected result : Because from our data IN has the longest border, combined length 20 50 40

state
Indiana

edit: The query i have made so far.

SELECT s.name
FROM state s
INNER JOIN boarder b
ON s.code = b.code1
WHERE SUM(b.length);

My thinking is I want to select the name, join it with borders table. Then get the sum. The two problems I see this this is it lists a bunch of names, where i only want one, and I'm not taking into account if the boarder on the other side. example being the first row in the boarders table. There are not two seperate rows of MI, IN and then IN, MI. So Should I try and do two subquires on either side, but how would i add the lengths?

CodePudding user response：

Assuming that the data isn't duplicated (so you don't have a row for both MI/IN and IN/MI) you can unpivot that table directly to get all the individual border lengths for each state:

select *
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u

and join that to the states table to get the matching names:

...
join state s on s.code = u.code

then use the sum() aggregate function to get the total length for each state:

select s.name, sum(u.length)
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u
join state s on s.code = u.code
group by s.name

and then order the result by descending total length, and pick the first row (i.e. the one with the highest value):

select s.name
from borders
unpivot (code for code_idx in (code1 as 1, code2 as 2)) u
join state s on s.code = u.code
group by s.name
order by sum(u.length) desc
fetch first 1 row only

NAME
Indiana

fiddle showing the intermediate steps, including how to get the name and total length if you want both.

If you might have ties then you need to decide whether to pick one of them - indeterminately, which this query will do, or determinately by adding say the state code to the order-by clause; or show them all - changing only to with ties.

If you're on an older version of Oracle that doesn't support the fetch syntax you can put the query in an inline view and select the first row from that.

The query i have made so far. ...

That would also work with a bit of tweaking, particularly changing your invalid where clause to an order by; but you don't need to use two subqueries as you suggested, you can modify the join condition to look at both columns in the borders table:

on (s.code = b.code1 or s.code = b.code2)

and then as above, order by the total length and fetch the first value:

select s.name
from state s
join borders b
on (s.code = b.code1 or s.code = b.code2)
group by s.name
order by sum(b.length) desc
fetch first 1 row only;

NAME
Indiana

fiddle

CodePudding user response：

You can UNPIVOT the borders table and then total the length for each state and find the maximum length(s) and then join to the state table to find the name:

SELECT s.name
FROM   state s
       INNER JOIN (
         SELECT code,
                SUM(length) AS total_length
         FROM   borders
         UNPIVOT (
           code FOR key IN (code1, code2)
         )
         GROUP BY code
         ORDER BY SUM(length) DESC
         FETCH FIRST ROW WITH TIES
       ) b
       ON s.code = b.code

Which, for the sample data:

CREATE TABLE state (name, code) AS
SELECT 'Michigan',  'MI' FROM DUAL UNION ALL
SELECT 'Indiana',   'IN' FROM DUAL UNION ALL
SELECT 'Illinois',  'IL' FROM DUAL UNION ALL
SELECT 'Ohio',      'OH' FROM DUAL UNION ALL
SELECT 'Wisconsin', 'WI' FROM DUAL;

CREATE TABLE borders (code1, code2, length) AS
SELECT 'MI', 'IN', 20 FROM DUAL UNION ALL
SELECT 'IL', 'IN', 50 FROM DUAL UNION ALL
SELECT 'MI', 'OH',  5 FROM DUAL UNION ALL
SELECT 'IN', 'OH', 40 FROM DUAL UNION ALL
SELECT 'WI', 'MI', 30 FROM DUAL;

Outputs:

NAME
Indiana

fiddle

CodePudding user response：

Maybe a simpler approach is to do a union in a CTE. I don't know the Oracle dialect of SQL but this should be standard our easy to make work:

with state_borders as
(
   select code1 as code, length from border
   union all select code2 as code, lenght from border
)
select top 1
   s.name,
   sum(b.length) as border_length
from
   state_borders as b
   left join state as s on s.code = b.code
group by
   s.name
order by
   sum(b.length) desc;

Not tested.

Be careful not to use reserved words as names in your database, like maybe name and state.