Record with same process id > 1

  • 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

  • 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
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

     

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

  • 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

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

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