1

I have a bunch of DVD rental + return times that I'd like to sort by duration, from the shortest rental time (amount of time between timestamps) to the longest rental time. Is AGE the function I should use?

Here's my dysfunctional SQL so far:

  SELECT f.title, age(r.return_date, rental_date) FROM film f
JOIN inventory i
    ON f.film_id = i.film_id
JOIN rental r
    ON i.inventory_id = r.inventory_id
WHERE age(r.return_date, r.rental_date)
GROUP BY f.title
ORDER BY age ASC;

Be nice please, I'm a total SQL noob :)

Splosion Lowbeam
  • 73
  • 1
  • 1
  • 10

1 Answers1

3

age() is fine, but:

  • I see no need for GROUP BY.
  • I see no need for WHERE
  • You need either an alias or to repeat the expression in the ORDER BY.

So:

SELECT f.title, age(r.return_date, r.rental_date)
FROM film f JOIN
     inventory i
     ON f.film_id = i.film_id JOIN
     rental r
     ON i.inventory_id = r.inventory_id
ORDER BY age(r.return_date, r.rental_date) ASC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786