SQL command works in Oracle not in MS SQL?

[ASmee]

select count(distinct exposureid) from iinstrumentidmaster where exposureid in (select exposureid from iinstrumentidmaster group by exposureid having count(exposureid) >1) order by exposureid

Can't work out why! MS SQL is not my speciality.

 

[SQLBill]

What error are you getting?

-SQLbill

 

[ASmee]

Sorry, I forgot that bit!

An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.

Same occurs on MS SQL 7 and 2000.

 

[SQLBill]

I don't understand why you are getting that error since you only have one ORDER BY and it's not in the subquery; you aren't using a view, and it doesn't appear to be part of a derived table.

However, give this a try:

select count(distinct exposureid)
from iinstrumentidmaster
where exposureid in (select TOP 100 PERCENT exposureid from iinstrumentidmaster group by exposureid having count(exposureid) >1)
order by exposureid

-SQLBill

 

[ASmee]

Ok, tried it and yet the following:

Column name 'iinstrumentidmaster.exposureid' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.

 

[nigelrivett]

select count(distinct exposureid) from iinstrumentidmaster where exposureid in (select exposureid from iinstrumentidmaster group by exposureid having count(exposureid) >1)

You are returning a single value here so there is no point in having an order by clause.

I think the same query is
select count(*)
from (select exposureid from iinstrumentidmaster group by exposureid having count(*) > 1) a

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.