Is it possible to select 2 grouped columns but distinct only one?

  • 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.

  • 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

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply