Write an SQL query to find the longest video among the 10 shortest videos in the video table (id, duration, author_id).

Условие

Write an SQL query to find the longest video among the 10 shortest videos in the video table (id, duration, author_id).

with sbq as ( select id, duration, author_id from video order by duration limit 10 ) select id, duration, author_id from sbq order by duration DESC limit 1

select id, duration, author_id from video order by duration limit 1 offset 9

shortest = Video.objects.order_by('duration').values('id')[:10][-1] result = Video.objects.filter(id__in=Subquery(shortest)).order_by("-duration").first()

Ответ

Select the ten shortest rows first, then select the longest row from that subset. Add an ID tie-breaker and decide how NULL durations and tables with fewer than ten videos should behave.

SELECT id, duration, author_id
FROM (
    SELECT id, duration, author_id
    FROM video
    WHERE duration IS NOT NULL
    ORDER BY duration ASC, id ASC
    LIMIT 10
) AS shortest
ORDER BY duration DESC, id DESC
LIMIT 1;

This returns the longest available video when fewer than ten non-NULL rows exist. OFFSET 9 LIMIT 1 instead returns nothing in that case. Django QuerySets do not support negative indexing: replace [:10][-1] with a subquery selecting ten IDs, then apply the outer ordering and .first().