Best way to get single MAX value + another column value from SQL

Question

I've got the following three tables defined:

table 'A':
-------------------
majorID    | bigint (primary key)
-------------------

table 'B':
-------------------
majorID    | bigint (foreign key to table 'A's majorID)
minorID    | bigint (primary key)
totalSize  | bigint
-------------------

table 'C':
-------------------
objectID   | bigint (primary key)
majorID    | bigint (foreign key to table 'A's majorID)
minorID    | bigint (foreign key to table 'B's minorID)
startPoint | bigint
length     | bigint
-------------------

What I'm looking to do is get a list of all rows in table 'B', but show how much space is left for each row.

The remaining space can be found by finding the highest "startPoint", adding the value o the "length" column for the row containing the highest "startPoint", then subtracting that combined value from the "totalSize" column in table 'B'

I am currently able to achieve this using the following code:

create table #results (MinorID bigint, MajorID bigint, RemainingSpace bigint)

DECLARE @MinorID bigint
DECLARE @TotalSpace bigint
DECLARE @MajorID bigint
DECLARE cur CURSOR FOR
SELECT MinorID, MajorID, TotalSize FROM B
OPEN cur


FETCH NEXT FROM cur INTO @MinorID,@MajorID, @TotalSpace

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @UsedSize bigint

    SELECT TOP 1 @UsedSize = StartPoint + [length] FROM C
    WHERE MinorID = @MinorID AND MajorID = @MajorID
    ORDER BY StartPoint DESC

    INSERT INTO #results VALUES (@MinorID,@MajorID,@TotalSpace - @UsedSize)

    FETCH NEXT FROM cur INTO @MinorID,@MajorID, @TotalSpace
END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #results
drop table #results       

The problem is that I expect these tables are going to get VERY large, and I realise that running a cursor over the tables probably isn't the fastest way to achieve what I want.

However, I'm struggling to find a better solution (Monday morning blues), and was hoping someone more awake / better at SQL than me can suggest a solution!

note: The table designs are not "set in stone", so if the only solution is to denormalize the data so that table 'B' keeps a record of it's "taken space", then I'm open to that...

EDIT:

I went with a modified version of the accepted answer, as follows:

SELECT B.*, coalesce(C.StartPoint + C.Length,0) AS UsedSize
    FROM TableB B
    LEFT JOIN 
    (
      SELECT *, DENSE_RANK() OVER(PARTITION BY C.MajorID, C.MinorID ORDER BY C.StartPoint DESC) AS Rank
      FROM TableC C
    ) C
    ON C.MajorID = B.MajorID
    AND C.MinorID = B.MinorID
    AND C.Rank = 1

Answer 1

WITH UsedSpace AS
(
SELECT minorID, MAX(startPoint + length) AS used
FROM C
GROUP BY minorID
)
SELECT B.minorID, totalSize - COALESCE(UsedSpace.used, 0)
FROM B LEFT JOIN UsedSpace ON B.minorID = UsedSpace.minorID

Answer 2

Maybe you are making things more complicated than they are. You are looking for the maximum startPoint for each minorID in order to add length and get thus the used size. But is it possible at all to have a lesser startPoint where its length is so big that adding both would exceed the maximum startPoint plus its length?

Is this possible ( max (startPoint) being lower than some other startPoint+length ):

minorID startPoint length
1       1          10
1       9          3         

If not, what I assume, you can simply subtract max(startPoint + length):

select 
  minorID, 
  totalSize, 
  totalSize - (select max(startPoint + length) from C where C.minorID = B.minorID) as space_left
from B;

EDIT: I just read your comment that sometimes no C for a B exists. To account for this you will have to use ISNULL or COALESCE:

select 
  minorID, 
  totalSize, 
  totalSize - coalesce((select max(startPoint + length) from C where C.minorID = B.minorID), 0) as space_left
from B;

Answer 3

You can use OUTER APPLY to get the top record from C ordered by StartPoint

SELECT  B.MajorID,
        B.MinorID,
        B.TotalSize,
        C.StartPoint,
        C.Length,
        SpaceRemaining = B.TotalSize - ISNULL(C.StartPoint + C.Length, 0)
FROM    B
        OUTER APPLY
        (   SELECT  TOP 1 C.StartPoint, C.Length
            FROM    C
            WHERE   B.MinorID = c.MinorID
            ORDER BY C.StartPoint DESC
        ) C;

Or you can use ROW_NUMBER to achieve the same result, depending on indexes etc, one may perform better than the other:

SELECT  B.MajorID,
        B.MinorID,
        B.TotalSize,
        C.StartPoint,
        C.Length,
        SpaceRemaining = B.TotalSize - ISNULL(C.StartPoint + C.Length, 0)
FROM    B
        LEFT JOIN 
        (   SELECT  C.MinorID,
                    C.StartPoint, 
                    C.Length,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY C.MinorID ORDER BY C.StartPoint DESC, Length DESC)
            FROM    C
        ) C
            ON B.MinorID = c.MinorID
            AND C.Rownumber = 1;

Examples on SQL Fiddle

Answer 4

SELECT B.MajorId, B.MinorId, B.totalSize-(C.length+C.startPoint) as Space 
from TABLEB B 
LEFT JOIN (SELECT MAX(startPoint) maxSP,majorid, minorid FROM TABLEC GROUP BY MajorId, MinorId) 
mxT ON B.majorID  = mxT.majorID AND B.minorId=mxt.minorId 
LEFT JOIN TABLEC C  on C.majorid=mxt.MajorId AND C.minorId=mxt>MinorId AND C.startPoint=mxT.maxSP

Answer 5

Maybe you can work with the DENSE_RANK.

In this query i am joining the table C with the extra column Rank. This column is given the value 1 if its the highest startpoint. In the (AND C.Rank = 1) we only extract that row.

SELECT B.*, (C.StartPoint + C.Length) AS UsedSize
FROM TableB B
INNER JOIN 
(
  SELECT *, DENSE_RANK() OVER(PARTITION BY C.MajorID, C.MinorID ORDER BY C.StartPoint DESC) AS Rank
  FROM TableC C
) C
ON C.MajorID = B.MajorID
AND C.MinorID = B.MinorID
AND C.Rank = 1