1

Here are the parameters for running a SQL query in the file 9.sql:

In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.

  • Your query should output a table with a single column for the name of each person.

  • People with the same birth year may be listed in any order.

  • No need to worry about people who have no birth year listed, so long as those who do have a birth year are listed in order.

  • If a person appeared in more than one movie in 2004, they should only appear in your results once.

Here is what my file currently looks like:

SELECT DISTINCT name
FROM
    people,
    stars,
    movies
WHERE people.id = stars.person_id
    AND stars.movie_id = movies.id
    AND year = 2004
ORDER BY birth ASC;

When I plug the query into phpliteadmin, it shows 17,965 rows, while the pset assignment explains that testing the query should output 18,013 rows. I'm wondering if anyone ran into this same issue and knows what the solution is.

Improve this question

2 Answers 2

Reset to default
6

Your DISTINCT would make the result records appear only once. But that means the name, and multiple stars might share a name. In other SQL dialects you could use DISTINCT ON (col1, col2) with an "ON" and a list of columns to use for the DISTINCT, but SQLite does not seem to support such thing.

I actually solved this by applying the DISTINCT to a sub-query that would differentiate people not by name, but by ID, and then find the names to those unique IDs.

Improve this answer
4
  • Did not really get what you mean to be honest. Can you elaborate a bit more?
    – Enis Arik
    Apr 19, 2020 at 17:01
  • The goal is to list the names of all the actors that starred in any 2004 movie. What I did instead was, in an inner SELECT, get a list of all the person_ids for those movies, apply DISTINCT to that, and then match that list against the people table to get the name and order by year of birth.
    – Blauelf
    Apr 19, 2020 at 19:57
  • I tried to do this, in a similar way to what I did on Problem #13, but I get the same result. Can you please share the inner loop? Thanks.
    – Kevin Fontecilla
    Jun 1, 2020 at 1:24
  • @KevinFontecilla There are many possible solutions. One way to solve 9.sql is to have an inner SELECT DISTINCT for the person_id (without the people table), and an outer SELECT combining this list of person IDs with their names. Using an IN operator for combining outer and inner SELECT would have the same effect as the DISTINCT, but I chose JOIN.
    – Blauelf
    Jun 2, 2020 at 18:10
8

I had the same problem. In the end I've solved it by getting rid of DISTINCT and using both ORDER BY and GROUP BY. Hope that helps!

Improve this answer
4
  • Yes, this works!
    – Enis Arik
    Apr 19, 2020 at 17:00
  • Can you please post the updated SQL query to test the count ?
    – Karan Dhanak
    May 20, 2020 at 12:56
  • How would you use "group by" to prevent duplicates?
    – Siwon Lee
    Jun 21, 2020 at 16:10
  • @SiwonLee GROUP BY would pick one record per unique value. In this query, you would use it with a field that's unique to each person. It's often used for a different purpose, to find the count, max, sum, avg, or similar for a number of records sharing certain properties.
    – Blauelf
    Aug 16, 2020 at 20:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .