CODEWITHSUNDEEP
sqlsql-server
Narek Maloyan
Narek Maloyan

Reputation: 183

SQL Server 2005 query help (group by)

I have videos from some country filmed by some author. I need to group by country and show the video with maximum duration from that country and it's author's name. I know how to select video with maximum duration, but I don't know how to add author's name.

Here's my script, which shows video with maximum duration from that country. All I need is to add author.

SELECT country.country_id, max(video.duration)
FROM VIDEO
JOIN COUNTRY ON country.country_id = video.country_id
JOIN AUTHOR  ON author.author_id   = video.author_id
GROUP BY country.country_id

Upvotes: 0

Views: 45

Answers (2)

Jason Pease
Jason Pease

Reputation: 62

There are a few ways to solve this, but a ROW_NUMBER is one of the simpler ways.

SELECT *
FROM (
   SELECT 
     ROW_NUMBER() OVER (PARTITION BY c.country_id ORDER BY v.duration) AS rn,
      *
   FROM VIDEO AS v
   JOIN AUTHOR AS a ON a.author_id   = v.author_id
) AS t
WHERE t.rn = 1

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

I believe that JOIN with country is not necessary, since you do not want any attributes except primary key from country and country_id is already in video.

One solution is to select appropriate rows from video using WHERE and dependent subquery with MAX

SELECT VIDEO.*, AUTHOR.name
FROM VIDEO v1
JOIN AUTHOR  ON author.author_id   = v1.author_id
WHERE video.duration = (
      SELECT max(v2.duration) 
      FROM VIDEO v2 
      WHERE v2.country_id = v1.country_id)

an if you want to avoid dependent subquery then you can use the following approach using GROUP BY

 SELECT VIDEO.*, AUTHOR.name
 FROM VIDEO v1
 JOIN AUTHOR  ON author.author_id   = v1.author_id
 JOIN (
   SELECT v.country_id, max(v.duration) maxduration
   FROM VIDEO v 
   GROUP BY v.country_id
 ) t ON v1.country_id = t.country_id and t.maxduration = v1.duration

Upvotes: 1

Related Questions