Trying to remove an inner SQL select statement

select, sql

Solution

You can use `JOIN` instead. Something like this:

SELECT h1.song_id, h1.event_type, h1.id 
FROM histories AS h1
INNER JOIN
(
   SELECT station_id, song_id, MAX(id) AS MaxId
   FROM histories 
   WHERE station_id = 187 
     AND event_type IN (1, 2) 
   GROUP BY station_id, song_id
)  AS h2  ON h1.station_id = h2.station_id 
         AND h1.song_id    = h2.song_id
         AND h1.id         = h2.maxid
ORDER BY h1.id;

Problem

I am making a music player where we have stations. I have a table called histories. It has data on the songs a user likes, dislikes or skipped. We store all the times that a person has liked a song or disliked it. We want to get a current snapshot of all the songs the user has either liked (event_type=1) or disliked (event_type=2) in a given station. The table has the following rows: - `id` (PK int autoincrement) - `station_id` (FK int) - `song_id` (FK int) - `event_type` (int, either 1, 2, or 3) Here is my query: ``` SELECT song_id, event_type, id FROM histories WHERE id IN (SELECT MAX(id) AS id FROM histories WHERE station_id = 187 AND (event_type=1 OR event_type=2) GROUP BY station_id, song_id) ORDER BY id; ``` Is there a way to make this query run without the inner select? I am pretty sure this will run a lot faster without it

Original source

Related problems