CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlselect
user1531921
user1531921

Reputation: 1432

How to specify a query for COUNT?

I've been searching for a bit without finding anything. I'm not 100% at home with SQL but here's what I want to do:

SELECT TOP (SELECT COUNT (DISTINCT Name) 
            FROM Logg WHERE Id= 1) 
* FROM Logg WHERE Id= 1

In other words, I want a single query, which first counts the number of rows with a distinct "Name" and then selects the top x amount of rows given that count.

How would I structure such a query?

Upvotes: 1

Views: 96

Answers (4)

user1364100
user1364100

Reputation: 

SELECT TOP 1 * FROM Logg WHERE Id= 1

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

Your query does work - you just need to specify a column list:

SELECT TOP (SELECT COUNT (DISTINCT Name) FROM Logg WHERE Id= 1) * -- Need to specify columns
FROM Logg WHERE Id= 1;

SqlFiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Are you trying to select all names that only appear once?

If so:

select name
from logg
where id = 1
group by name
having count(*) = 1;

Upvotes: 0

Manoj Pandey
Manoj Pandey

Reputation: 1397

Just separate out the query into 3 parts:

DECLARE @cnt INT
SELECT @cnt = COUNT (DISTINCT TestNamn) FROM SelfTestLogg WHERE ApplikationId = 1
SELECT TOP (@cnt) * FROM SelfTestLogg WHERE ApplikationId = 1

Upvotes: 0

Related Questions