Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-12-19 : 04:25:32

For each country, find the year, in which the maximal number of ships had been launched.
In the case of more than one year in question, take a minimal year. Result set: country, number of ships, year

classes ships
======== ========
class(pri key) name(name of the ship)pri key
country class(ship class name) foreign key ref
launched(year the ship launched)

relation:1->M

i have tried the below query,but i think this is not the correct

select country,count(name) as qty,year(launched)as year
from ships s join classes c
on s.class=c.class
group by country,year(launched)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-19 : 04:54:47

Try this:-

SELECT tmp.Country,tmp.ShipCount,tmp.Year FROM
(
     SELECT ROW_NUMBER() OVER (PARTITION BY t.Country ORDER BY ShipCount DESC) AS 'RowNo',
     t.Country,
     t.Year,
     t.shipcount
     FROM
     (
          SELECT   c.Country,
          YEAR(s.launched) AS 'Year',
          Count(s.name) AS 'ShipCount'
          FROM classes c
          INNER JOIN ships s
         ON c.class=s.class
        GROUP BY c.Country,YEAR(s.launched)
      )t
)tmp
WHERE tmp.Row_No=1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-19 : 04:55:29

Another homework ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources