Record with same process id > 1

Question

Record with same process id > 1

jsshivalik

SSChasing Mays

Points: 612
More actions
August 2, 2022 at 10:49 am

#4071285

Hi
I have below data and i want Users with same process id > 1.
User Date ProcessId
usr4 2022-08-02 00:00:00.000 12168
usr4 2022-08-02 00:00:00.000 11540
usr5 2022-08-02 00:00:00.000 3552
usr1 2022-08-02 00:00:00.000 2061
usr1 2022-08-02 00:00:00.000 2061
usr1 2022-08-02 00:00:00.000 2061
usr1 2022-08-02 00:00:00.000 2061
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Maybe this?

select [User]
from mytable
group by [User]
having count(ProcessId) > count(distinct ProcessId);

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ScottPletcher SSC Guru Points: 101118 More actions · Answer 2

SELECT User, ProcessId, COUNT(*) AS ProcessDupCount

FROM dbo.table_name

GROUP BY User, ProcessId

WHERE ProcessId > 1

HAVING COUNT(*) > 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jsshivalik SSChasing Mays Points: 612 More actions · Answer 3

Hi

It is giving me Incorrect Syntax near where

SELECT User, ProcessId, COUNT(*) AS ProcessDupCount

FROM dbo.table_name

GROUP BY User, ProcessId

WHERE ProcessId > 1

HAVING COUNT(*) > 1

Thanks

ratbak Ten Centuries Points: 1381 More actions · Answer 4

The WHERE clause has to precede the GROUP BY CLAUSE -- where filters data before it is aggregated

SELECT...
FROM...
JOIN...
WHERE...
GROUP BY...
HAVING...
ORDER BY...

jsshivalik SSChasing Mays Points: 612 More actions · Answer 5

Hi

I am getting error - Each GROUP BY expression must contain at least one column that is not an outer reference.

SELECT User, ProcessId, COUNT(*) AS ProcessDupCount

FROM dbo.user

WHERE ProcessId > 1

GROUP BY User, ProcessId

HAVING COUNT(*) > 1

Thanks