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

Yep. For each row in Table 'A', there can be n rows in Table 'B', and for each row in Table 'B', there can also be n rows in Table 'C'. — Sk93
– Sk93, Commented Jan 27, 2014 at 11:16
Effectively yes. It's denormalised data that I've included to simplify the sql required to get the majorID when looking up data using only the objectID (eg, select * from C gives me everything I need, wheras without it, i'd have to inner join to B). Not an issue if it's removed. — Sk93
– Sk93, Commented Jan 27, 2014 at 11:19

GercoOnline · Accepted Answer · 2014-01-27 11:23:38Z

4

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

answered Jan 27, 2014 at 11:23

GercoOnline

3031 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-01-27 12:09:58Z

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;

edited Jan 27, 2014 at 12:09

answered Jan 27, 2014 at 11:37

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

1 Comment

Thomas Padron-McCarthy Over a year ago

I like this solution best. Short, simple and uses no overly advanced or SQL Server-specific constructs.

Thomas Padron-McCarthy · Accepted Answer · 2014-01-27 12:32:01Z

2

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

edited Jan 27, 2014 at 12:32

answered Jan 27, 2014 at 11:33

Thomas Padron-McCarthy

27.7k8 gold badges55 silver badges77 bronze badges

2 Comments

Thomas Padron-McCarthy Over a year ago

Someone downvoted, so it seems I missed something. Not sure what.

Sk93 Over a year ago

Wasn't me.. but thanks for the answer anyways - will try and vote /accept accordingly :)

GarethD · Accepted Answer · 2014-01-27 11:35:30Z

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

DhruvJoshi · Accepted Answer · 2014-01-27 11:25:22Z

0

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

answered Jan 27, 2014 at 11:25

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related