locked
How can I get the records from the table and select other group of record from the same table RRS feed

  • Question

  • I have  the following tables . How can I get the distinct departmentID for the userid =1 . If  IsDefault value is true then the other  same department with default value false should not be listed . I want to list every distinct department with defaultValue but if the department has Isdefault = 1, then the same department with IsDefault =0 should be ignored. Please help to find the sql 

    create table  DepartmentLink
    (Depot int,Departement int, IsDefault bit, Userid int)
    
    Depot     Department          IsDefault        UserId
    1              1                   1		1
    1              2                   0		1
    1              3                   0		1
    2              1                   0		1
    2              2                   0		1
    2              3                   0		1
    3              1                   0		1
    3              4                   0		1
    
    1              1                   0		2
    1              2                   1		2
    1              3                   0		2
    2              1                   0		2
    2              2                   0		2
    2              3                   0		2
    3              1                   0		2
    3              4                   0		2
    
    

    So the result should be

                 Department       IsDefault        UserId
                  1                   1		1
                  2                   0		1
                  3                   0		1
                  4                   0		1

    Regards

    Pol




    polachan

    Thursday, July 30, 2020 11:26 AM

All replies

  • It sounds like you want:

    SELECT UserId, Department, MAX(IsDefault) AS IsDefault
    FROM   tbl
    GROUP  BY UserId, Department

    if IsDefault is bit, you will get an error message, which you can work around this way:

    SELECT UserId, Department, MAX(CAST(IsDefault AS tinyint)) AS IsDefault
    FROM   tbl
    GROUP  BY UserId, Department


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi N Thursday, July 30, 2020 12:35 PM
    • Unproposed as answer by Naomi N Thursday, July 30, 2020 12:37 PM
    • Proposed as answer by Naomi N Thursday, July 30, 2020 12:37 PM
    Thursday, July 30, 2020 12:09 PM
  • Hi polachan,

    Please also have a try with below:

    create table  DepartmentLink
    (Depot int,Departement int, IsDefault bit, Userid int)
    
    insert into DepartmentLink values
    (1,1,1,1),
    (1,2,0,1),
    (1,3,0,1),
    (2,1,0,1),
    (2,2,0,1),
    (2,3,0,1),
    (3,1,0,1),
    (3,4,0,1),
    (1,1,0,2),
    (1,2,1,2),
    (1,3,0,2),
    (2,1,0,2),
    (2,2,0,2),
    (2,3,0,2),
    (3,1,0,2),
    (3,4,0,2)
    
    SELECT * FROM (
    SELECT Departement, IsDefault,UserId FROM DepartmentLink
    EXCEPT
    SELECT A.Departement, A.IsDefault,A.UserId FROM DepartmentLink A
    INNER JOIN DepartmentLink B ON A.Departement=B.Departement AND A.Userid=B.Userid 
    WHERE A.IsDefault<B.IsDefault) C 
    WHERE USERID=1
    ORDER BY USERID,Departement,IsDefault
    
    /*
    Departement	IsDefault	UserId
    1	1	1
    2	0	1
    3	0	1
    4	0	1
    */

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Friday, July 31, 2020 2:14 AM
  • Hi polachan,

    Could you please provide any update about this?

     

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

     

    Thank you for understanding!

     

     

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 3, 2020 12:56 AM