Alternative to using DISTINCT with COUNT Function
-
Is there another way to get the same result from the following query. The reason I ask is because I am using SQL Mobile Everywhere, and the DISTINCT keyword can't be used within a COUNT function unfortunately.
SELECT COUNT(DISTINCT QuestionTreeUID) FROM tbl_NSP_Answer;
Thanks
-
Can U do Derived Table
Select count(*)
From (select distinct QuestionTreeUID FROM tbl_NSP_Answer) T
-
Or maybe a variable to store the @@ROWCOUNT ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
You forgetting people that I am using SQL Mobile Everywhere, so the use of sub-queries (only allowed in IN Clauses) isn't permitted, and @@ROWCOUNT doesn't work
Thats why I need a simple SQL solution, if at all possible
Thanks
-
Simple solution is proper database design.
You suppose to have separate table with unique identfiers for your "QuestionTree"s.
Probably you have, if you reference QuestionTreeUID.
Then you select COUNT(*) from that table WHERE EXISTS (select 1 from tbl_NSP_Answer ....)
Hope EXISTS works in SQL Mobile Everywhere
_____________
Code for TallyGenerator -
If temp tables are allowed, then you can do...
select distinct(column)
into #table
from table
select count(*) from #table
**ASCII stupid question, get a stupid ANSI !!!** -
If it comes to worst... create a perm temp table with the spid as clustered index. or maybe a table variable (not that I think you're allowed to do that at this point
).
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply