August 19, 2010 at 1:49 pm
I am braincramping on something that seems like it should be simple and am hoping someone here can offer some direction. I have a sql statement that looks like this:
SELECT TOP 20 movie_id
, COUNT(tr_id) AS times_viewed
FROM trailer_requests
GROUP BY movie_id
ORDER BY COUNT(tr_id) DESC
This query returns two columns, something like:
movie_idtimes_viewed
730467452
955273006
737812707
728212575
769612536
A new column, named trailer_speed_id has been added to this table, to signify different speeds. Now, I need to pull back the movie_id, trailer_speed_id and times_viewed for the top 5 distinct movie_id values.
Here is what I have so far:
CREATE TABLE #trailer_counts
(
movie_id INT
, trailer_speed_id INT
, times_viewed INT
)
INSERT INTO #trailer_counts
SELECT movie_id
, trailer_speed_id
, COUNT(trailer_speed_id)
FROM trailer_requests
GROUP BY movie_id
, trailer_speed_id
SELECT TOP 5 movie_id
, trailer_speed_id
, times_viewed
FROM #trailer_counts
ORDER BY times_viewed DESC
DROP TABLE #trailer_counts
This query returns three columns, something like:
movie_idtrailer_speed_idtimes_viewed
7304610194396
9552710783006
722266722497
730468702059
562667961825
As you can see, I am getting the 73046 ID multiple times. If the ID exists already, I need to skip it. Does anyone have any ideas about how I can accomplish this? I'm really trying not to use a cursor.
August 19, 2010 at 3:39 pm
I took the liberty of attempting to convert your table definition and actual results and post those per instructions contained in the first link in my signature block. That said is this what you need?
CREATE TABLE #Trailer_Requests( movie_id INT ,trailer_speed_id INT,times_viewed INT)
INSERT INTO #Trailer_Requests
SELECT 73046, 1019,100 UNION ALL
SELECT 73046, 1019, 4396 UNION ALL
SELECT 95527, 1078, 3006 UNION ALL
SELECT 72226, 672, 2497 UNION ALL
SELECT 73046, 870, 2059 UNION ALL
SELECT 56266, 796, 1825
SELECT movie_id, MIN(trailer_speed_id), MAX(times_viewed)
FROM #trailer_requests GROUP BY movie_id
DROP TABLE #Trailer_Requests
Results:
movie_id Min speed Max Viewed
----------- ----------- -----------
56266 796 1825
72226 672 2497
73046 870 4396
95527 1078 3006
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply