If Record is Duplicate Skip All The Other Record except 1 Row Matching Specific Condition

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I've Requirement Where I Set Permissions of Each Control to Particular USER OR GROUP of USERS....

I'm Querying SQL SERVER with This Query

SQL

SELECT type,
       pagecontrolmaster.controlnameid,
       pagecontrolmaster.controltype,
       pagecontroltransaction.visible,
       pagecontroltransaction.enabled,
       pagecolumnmaster.columnno
FROM   dbo.pagecontrolmaster
       INNER JOIN dbo.pagedetails
               ON dbo.pagecontrolmaster.pageid = dbo.pagedetails.pageid
       INNER JOIN dbo.pagecontroltransaction
               ON dbo.pagecontrolmaster.controlid =
                  dbo.pagecontroltransaction.controlid
       INNER JOIN dbo.userlogindetails
               ON CASE
                    WHEN pagecontroltransaction.type = 'G'
                         AND pagecontroltransaction.forusergroupid =
                             userlogindetails.usergroupid
                  THEN 1
                    WHEN pagecontroltransaction.type = 'U'
                         AND pagecontroltransaction.forusergroupid =
                             userlogindetails.loginid THEN
                    1
                  END = 1
       LEFT OUTER JOIN dbo.pagecolumnmaster
                    ON dbo.pagecontroltransaction.columnid =
                       dbo.pagecolumnmaster.columnid
WHERE  ( dbo.userlogindetails.loginid = 130 )
       AND ( dbo.pagedetails.pagename = 'Lease Contract Master' )
GROUP  BY dbo.pagecontrolmaster.controlnameid,
          dbo.pagedetails.pagename,
          dbo.pagecontrolmaster.controltype,
          dbo.userlogindetails.loginid,
          visible,
          enabled,
          columnno,
          type
--HAVING    (Case When Count(ControlnameID)>1 And Type = 'U' Then 1
--				When Count(ControlnameID)=1 And Type = 'U'OR Type = 'G' Then 1 End =1)
ORDER  BY Max(dbo.pagecontroltransaction.type),
          dbo.pagecontrolmaster.controltype,
          dbo.pagecontrolmaster.controlnameid

Which Gave Me Records Like This

---------------------------------------------------------------
TYPE    ControlNameID   Control Type  VISIBLE  ENABLE  ColumnNo
---------------------------------------------------------------
G	txtNameLeaseCo	TextBox	        1	0	NULL
U	tcMain	        TabContainer	0	1	1
U	txtNameLeaseCo	TextBox	        0	1	NULL
G	txtAddress	TextBox	        1	0	NULL

I want Results Like Below

---------------------------------------------------------------
TYPE    ControlNameID   Control Type  VISIBLE  ENABLE  ColumnNo
---------------------------------------------------------------
G   txtAddress	        TextBox	        1	  0	NULL
U   tcMain              TabContainer    0         1      1
U   txtNameLeaseCo      TextBox         0         1     NULL

Difference Between This Two Results is that If Count of ControlNameID>1 Then Select Records for That particular ControlNameID who's TYPE='U'

You Can See in My Query That I Tried Using Case (in Commented Part)in Having Clause but the Problem I faced was When I'm using Having Count(ControlNameID)>1 Then It returns No Result...
Can Any One Explain me why Having Count(ControlNameID)>1 is not performing well In This Query and How Can I get My Desired Results.... :)

Posted 26-Nov-14 4:10am

prashantttt

Updated 26-Nov-14 4:16am

Tomas Takac

v2

Add a Solution

Comments

Shweta N Mishra 26-Nov-14 10:18am

in Above result which you expect txtAddress(ControlNameID) Type is not U then also its in your result, How?

prashantttt 26-Nov-14 10:23am

Count of txtAddress is 1

if Count of Any ControlNameID is 1 then No matter what type is, it should be Shown in Result....

Maciej Los 26-Nov-14 10:51am

Post sample data (input).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Accepted Answer · 2014-11-26T04:40:00

You could try something like this:

SQL

with aa as(
--put the original query here
	select 'G' Type, 'txtNameLeaseCo' ControlNameID, 'TextBox' [Control Type], 1 Visible, 0 Enable, NULL ColumnNo
	union all
	select 'U' Type, 'tcMain' ControlNameID, 'TextBox' [Control Type], 0 Visible, 1 Enable, 1 ColumnNo
	union all
	select 'U' Type, 'txtNameLeaseCo' ControlNameID, 'TextBox' [Control Type], 0 Visible, 1 Enable, NULL ColumnNo
	union all
	select 'G' Type, 'txtAddress' ControlNameID, 'TextBox' [Control Type], 1 Visible, 0 Enable, NULL ColumnNo
)
, bb as(
 select *,
 count(*) over (partition by ControlNameID) cnt
 from aa
)
select 
	Type,
	ControlNameID,
	[Control Type],
	Visible,
	Enable,
	ColumnNo
from bb
where cnt =  1
or (cnt = 2 and Type = 'U')
order by
	Type,
	ControlNameID
;

Refer to SQL Server Query question[^] - where the inspirational idea came from