query does not work.

[drats]

hello people,
when i run this below written query then i get error as
"ORDER BY clause is invalid in views,inline functions,derived tables, subqueries and common table expression unless TOP or for Xml is also specified".

i want to have the top 5 result based on the Gross_written_premium .

can someone please help me out as m new to sql server 2005.
please !!!!!

My query is

select dp.product_lob As lobs,

           SUM(fs.written_premium_amt_ytd) As Gross_written_premium from

(select 
           dp.product_lob As lobs,

           SUM(fs.written_premium_amt_ytd) As Gross_written_premium



FROM      dbo.Fct_Summary as fs
          inner join Dim_product as dp on ( fs.product_key = dp.product_key )
          inner join Dim_Calendar as dc on ( fs.calendar_key = dc.calendar_key)

where dc.calendar_month_name like $P{Month}

Group by  product_lob,

          written_premium_amt_ytd

order by   SUM(fs.written_premium_amt_ytd) desc)

from
dbo.Fct_Summary as fs
          inner join Dim_product as dp on ( fs.product_key = dp.product_key )
          inner join Dim_Calendar as dc on ( fs.calendar_key = dc.calendar_key)

where rownum<=5

[bhartman21]

Try it something like this...

SELECT TOP 5 
	dp.product_lob AS lobs,
	SUM(fs.written_premium_amt_ytd) AS Gross_written_premium 
FROM 
	(
	SELECT 
		dp.product_lob As lobs,
		SUM(fs.written_premium_amt_ytd) As Gross_written_premium
	FROM dbo.Fct_Summary AS fs
		INNER JOIN Dim_product AS dp ON ( fs.product_key = dp.product_key )
		INNER JOIN Dim_Calendar AS dc ON ( fs.calendar_key = dc.calendar_key)
	WHERE dc.calendar_month_name like $P{Month}
	GROUP BY product_lob, written_premium_amt_ytd
	/* 
	--ORDER BY SUM(fs.written_premium_amt_ytd) DESC 
		YOU DON'T WANT TO ORDER INSIDE YOUR SUB-QUERY,...
		 BUT GROUPING SHOULD BE OK 
	*/
	)
ORDER BY Gross_written_premium DESC

[tesuji]

Hello

first, put your code between code tags and improve formatting:

select dp.product_lob As lobs,

SUM(fs.written_premium_amt_ytd) As Gross_written_premium [B]from[/B]

--xxx-- opening parenthesis 
(select
dp.product_lob As lobs,

SUM(fs.written_premium_amt_ytd) As Gross_written_premium



FROM dbo.Fct_Summary as fs
inner join Dim_product as dp on ( fs.product_key = dp.product_key )
inner join Dim_Calendar as dc on ( fs.calendar_key = dc.calendar_key)

where dc.calendar_month_name like $P{Month} -- what's that?

Group by product_lob,

written_premium_amt_ytd -- not necessary for it is used in sum()

order by SUM(fs.written_premium_amt_ytd) desc -- not allowed in that clause!
) -- closing parenthesis from --xxx--, therefore this is missing here: AS musthavename

from  -- here illegal for you already have a from clause (bold-marked)
dbo.Fct_Summary as fs
inner join Dim_product as dp on ( fs.product_key = dp.product_key )
inner join Dim_Calendar as dc on ( fs.calendar_key = dc.calendar_key)

where rownum<=5  -- Oracle knows this, but MS Sql Server doesn't it?

Second, no matter whether the overall select may ever produce usefull results, there are too much mistakes. The ones I could figured out are red-coloured + comments (--). You may correct your query but I am afraid it will not give satisfied results. Why do you interleave two almost same queries into each other?

I think it is a good idea to test select parts of this complex statement first, then
mount them together.