Incorrect syntax near the keyword 'select' while execuing query

Question

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

Answer 1

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).

Answer 2

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

Answer 3

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