Incorrect syntax near Group for select query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Incorrect syntax near Group for select query

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-08-22 : 12:14:40

I am trying to find are there multiple records i the table for that userid:, since that table must only have one record per userid:

select *
from tab_ccsnetuserroles
where userid in (select userid from tab_ccsnetuserroles having count(*) > 1 group by userid)

when i execute the above query it is giving message incorrect syntax near Group.

what am i doing wrong.

Thank you very much for the information.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-08-22 : 12:26:53

OK got it: this works fine:

select * from TAB_ccsNetUserRoles
where userid in (select userid from TAB_ccsNetUserRoles group by userid having count(*) > 1 )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 12:31:16

Learn to use the new SQL Server 2005 features.

select <col list here> from (
SELECT <col list here>, row_number() over (partition by user id order by userid) AS recid
FROM tab_ccsneturdersroles
) as d where recid = 2

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 12:33:34

Or even better

;with c (col1, col2, col3, ..., recid)
AS (
SELECT col1, col2, col3, ..., row_number() over (partition by user id order by userid)
FROM tab_ccsneturdersroles
)

select * from c
where recid = 2

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources