Sql query for the last person in a line-CodePudding

Table of persons who a staying in a line to an elevator:

Queue:

 ----------- ------------- -------- ------ 
| person_id | person_name | weight | num  |
 ----------- ------------- -------- ------ 
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
 ----------- ------------- -------- ------

num is the number in the line. The elevator is able to lift up to 1000 lbs. Who is the last person coming to the elevator?

Need an sql query. (if possible without window functions)

result: 
 ------------- 
| person_name |
 ------------- 
| John Cena   |
 -------------

CodePudding user response：

You don't mention the specific database but a generic version of the query can look like:

select person_name
from (
  select *, row_number() over(order by num desc) as rn
  from (
    select t.*, sum(weight) over(order by num) as aw from t
  ) x
  where aw <= 1000
) y
where rn = 1

CodePudding user response：

without window functions

That makes this awkward, but still possible. I'll build the result in steps, explaining as I go.

Begin by joining the table to itself, where the join condition is everyone at the current position in line or earlier:

SELECT *
FROM Queue q1
JOIN Queue q2 on q2.Num <= q1.Num

Now we can GROUP this by the needed q1 fields, and SUM the weights of q2:

SELECT q1.Num, q1.person_id, q1.person_name, SUM(q2.Weight) as TotalWeight
FROM Queue q1
JOIN Queue q2 on q2.Num <= q1.Num
GROUP BY q1.Num, q1.person_id, q1.person_name

Even with just six rows of sample data, that's already 21 rows materialized needed to create this calculation, so you can see why window functions would be preferred.

Next we use a HAVING clause to limit the total weight.

SELECT q1.Num, q1.person_id, q1.person_name, SUM(q2.Weight) as TotalWeight
FROM Queue q1
JOIN Queue q2 on q2.Num <= q1.Num
GROUP BY q1.Num, q1.person_id, q1.person_name
HAVING SUM(q2.Weight) <= 1000

Finally, we can order things so our target record is first, and only take that first row:

SELECT TOP 1 q1.Num, q1.person_id, q1.person_name, SUM(q2.Weight) as TotalWeight
FROM Queue q1
JOIN Queue q2 on q2.Num <= q1.Num
GROUP BY q1.Num, q1.person_id, q1.person_name
HAVING SUM(q2.Weight) <= 1000
ORDER BY Num DESC

See it work here:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=42efb4e2e71e95dcb54d59e780267857