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

• Chris-491122

  SSC Enthusiast

  Points: 128

  More actions

  August 19, 2010 at 1:49 pm

  #72933

  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.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 19, 2010 at 3:39 pm

  #1209396

  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]