I am getting Incorrect syntax near the keyword 'select' after executing the following code.
declare @c int
SELECT @c = COUNT(*)
FROM (select id, max(date_stored)
from table B
INNER JOIN table P ON B.id = P.id
where id = 3)
select @c
I want to select total no of records having max stored dates in database. Can any one plz tell what I am doing wrong
you need to alias the subquery
declare @c int
SELECT @c = COUNT(*) FROM
(
select id, max(date_stored)
from
table B
INNER JOIN
table P
ON
B.id = P.id
where
id = 3
) x --alias
select @c
I still don't understand why toy are doing the group by and max in the subquery if all you need is a count
declare @c int
SELECT @c = COUNT(aName.[ID]) FROM
(
select [B.id], max(B.date_stored)
from table B
INNER JOIN table P ON B.id = P.id
where B.id = 3
-- group by [id] ???
) aName
select @c
SET @C = SELECT COUNT(*) FROM ...
Also, do you need to do a GROUP BY in that inner SELECT? What is the "max()" call supposed to mean? What table is that "date_stored" field in? I think you should get that inner query worked out first, and then move on to getting the counts.
If you're trying to count records where the date value is equal to the newest of all date values for a given "id" value, then you're going to need to make that explicit (unless SQL Server 2005 has some neat new feature I don't know about).
idvalue coming from, because not specifying will result in an "ambiguous column reference" error seeing that the column exists in both tables.