1

I have two tables. One called movies, and the other called movies_downloads. Here is an Example of the Tables:

movies:

╔═══╦════════════╗
║   ║ name       ║
╠═══╬════════════╣
║ 1 ║ Movie1     ║
║ 2 ║ Movie2     ║
║ 3 ║ Movie3     ║
╚═══╩════════════╝

movies_downloads:

╔═════════╦════════════╗
║ movieId ║   id (ai)  ║
╠═════════╬════════════╣
║   1     ║     1      ║
║   1     ║     2      ║
║   2     ║     3      ║
║   3     ║     4      ║
╚═════════╩════════════╝

Like you can see, it might be two rows in movies_downloads of the same movie. (in this case, it's movie #1)

I want to select all of the movies from the end to the begining, and I want to join the downloads table, and take the last row (biggest id) from it.

In this case, the result will be:

MovieId     Name     movies_downloads.id
   3       Movie3       4      
   2       Movie2       3      
   1       Movie1       2

The problem is: that instead of giving me the id#2 (beacuse he's the last), it gives the id#1. (which is the first)

My Code:

SELECT      `movies`.*, `movies_downloads`.`id`
FROM        `movies`
JOIN        `movies_downloads`
ON          `movies`.`id` =  `movies_downloads`.`movieId` 
GROUP BY    `movies`.`id` 
ORDER BY    `movies_downloads`.`id`
DESC 
LIMIT 10

BTW, I use mysql engine, if it matters.

Thank you!

HTMHell
  • 5,761
  • 5
  • 37
  • 79
  • 1
    This may be a design problem: there's nothing in the sample data to indicate whether `Link .` or `Link !` is the most recent. The order you get when you do `SELECT *` without an `ORDER BY` is arbitrary, so if you need a non-arbitrary order you need something to order *by*. I'd suggest adding a `TIMESTAMP` column to record when the download happened. – Ed Gibbs Dec 19 '13 at 18:56

1 Answers1

1

In order to be able to show the most recent download, you will need to add an additional column to movie_downloads that can determine insert order. This would typically be an auto-increment Id column or a timestamp column. I will assume that you are going to change the schema to add an Id column for movie_downloads. You can then use this to show the data that you want with the following query

In order to return the joined row that gives the highest value in a specific column, you will need to use one of the techniques given in this answer to this same problem. Here is a demonstration using the first technique shown there.

SELECT      m.Id, m.Name, d.Link
FROM        movies as m
JOIN        movies_downloads as d ON m.id = d.movieId 
JOIN   (SELECT MovieId, max(Id) as maxId from movies_downloads GROUP BY MovieId) as d2
            ON d.movieId = d2.movieId AND d.Id = d2.MaxId
ORDER BY    m.id desc
LIMIT 10

The second join here is the key - it is on the same movies_downloads table, and is giving the max(id) for each movieId in that table. This is joined to the first join of the movies_downloads table to limit the rows included from there into the select list to just one - the row that you want (with the highest id value).

Community
  • 1
  • 1
Yaakov Ellis
  • 40,752
  • 27
  • 129
  • 174
  • 1
    i think you have an extra DESC – Hituptony Dec 19 '13 at 19:16
  • @Hituptony @YaakovEllis♦ I do have an `id` column in `movies_downloads` table, and I did like you said: `ORDER BY movies_downloads.id DESC, movies.id DESC`, but it still not working... – HTMHell Dec 19 '13 at 19:26
  • @ArielAharonson I changed it up with a totally different approach. – Yaakov Ellis Dec 19 '13 at 19:29
  • I didn't say order by ID twice..? – Hituptony Dec 19 '13 at 19:31
  • @Hituptony My bad. Anyway, your answer isn't goos for me, because I want to order it by id, and not the `links` column. – HTMHell Dec 19 '13 at 19:33
  • @ArielAharonson check out what I wrote - it is ordering by id in the second table, not the links column – Yaakov Ellis Dec 19 '13 at 19:34
  • if you're sorting by movieid will that prove to sort your links the way you want in EVERY case? – Hituptony Dec 19 '13 at 19:35
  • @Hituptony I just added an `order by movie.id desc` which should determine the order of the rows shown per your requirements – Yaakov Ellis Dec 19 '13 at 19:37
  • @YaakovEllis I changed it like you said, but it still shows the first row from `movies_downloads`. – HTMHell Dec 19 '13 at 19:43
  • @ArielAharonson you added another column to `movies_downloads` that can be used to determine which is first and which is last? If so, please update the question to show the new schema and data. – Yaakov Ellis Dec 19 '13 at 19:46
  • @YaakovEllis I had the column from the first place. I just didn't write it in the question. I'll update it now. – HTMHell Dec 19 '13 at 19:49
  • @ArielAharonson I made a small but important mod to give an alias to the aggregate `max(id)` in the subquery so that it will be referenced properly in the main query. Should work now. – Yaakov Ellis Dec 19 '13 at 19:53