Click here to Skip to main content
15,172,004 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on SQL server 2012 i need to use group by instead of distinct

so how to do that please

query working without any problem and give me result i need but I need to use group by instead

of distinct on last statement executed in exec @SQL

and if there are advice about indexes can help me to enhance performance is preferable

What I have tried:

SQL
IF OBJECT_ID('[dbo].[Codes]') IS NOT NULL
DROP TABLE [dbo].[Codes]
IF OBJECT_ID('[dbo].[gen]') IS NOT NULL
DROP TABLE [dbo].[gen]
IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL
DROP TABLE [dbo].[PartAttributes]

IF OBJECT_ID('[dbo].[Allfeatures]') IS NOT NULL
DROP TABLE [dbo].[Allfeatures]
IF OBJECT_ID('dbo.AllData') IS NOT NULL
DROP TABLE dbo.AllData
IF OBJECT_ID('dbo.Condition') IS NOT NULL
DROP TABLE [dbo].Condition
IF OBJECT_ID('dbo.core_datadefinition_Detailes') IS NOT NULL
DROP TABLE core_datadefinition_Detailes


CREATE TABLE [dbo].[Codes](
[ZPLID] [int] NULL,
[Code] [varchar](20) NULL,
[Proceed] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'32111502', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100000', 1)
INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100050', 1)

CREATE TABLE core_datadefinition_Detailes(
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ColumnName] [nvarchar](500) NOT NULL,
[ColumnNumber] [int] NOT NULL,

 CONSTRAINT [PK_Core_DataDefinition_Details] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
)
insert into core_datadefinition_Detailes([ColumnNumber],[ColumnName])
values
(202503,'Product Shape Type'),
(1501170111,'Type'),
(1501170046,'Maximum Peak Pulse Current'),
(202504,'Package Family')







CREATE TABLE [dbo].[gen](
[TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL,
[CodeTypeID] [int] NULL,
[RevisionID] [bigint] NULL,
[Code] [varchar](20) NULL,
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[ZfeatureType] [nvarchar](200) NULL,
[EStrat] [nvarchar](2500) NULL,
[EEnd] [nvarchar](2500) NULL
) ON [PRIMARY]


GO
SET IDENTITY_INSERT [dbo].[gen] ON 
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7565,  849774, 307683692, N'8541100050', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7566,  849774, 307683692, N'8541100050', 4239, 202504, N'Package', N'Not In(''Die'',''Wafer'',''N/A'')', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],   [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7567,  849774, 307683692, N'8541100050', 4239, 1501170111, NULL, N'=''Zener''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7577, 1019997, 313023938, N'8541100000', 4239, 202503, N'Package', N'<>''IC''', N'')
INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID],  [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (9497, 1809942, 329888149, N'32111502', 4239, 1501170111, NULL, N'=''Zener''', N'')

SET IDENTITY_INSERT [dbo].[gen] OFF

CREATE TABLE [dbo].[PartAttributes](
[PartID] [int] NOT NULL,
[ZfeatureKey] [bigint] NULL,
--[FeatureName] [nvarchar](200) NOT NULL,
[AcceptedValuesOption_Value] [float] NULL,
[FeatureValue] [nvarchar](500) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202503, NULL, N'Discrete')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170111, NULL, N'Zener')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170046, 3, N'3A')
INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202504, NULL, N'SOT')

CREATE TABLE [dbo].[Allfeatures](
[ZPLID] [int] NULL,
[ZfeatureKey] [bigint] NULL,
[FeatType] [int] NULL,
[AcceptedValueID] [int] NULL,
[IsNumericValues] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, NULL, 0, 0, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202503, NULL, 33, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202504, NULL, 34, 0)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170046, 2044, 814, 1)
INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170111, 2043, 155, 0)


CREATE TABLE dbo.AllData
(

PartID INT,
Code VARCHAR(20),
CodeTypeID INT,
RevisionID BIGINT,
ZPLID INT,
ConCount INT,
FeatureName nvarchar(500),
FeatureValue  nvarchar(500)

)

UPDATE Codes
SET 
Proceed=0

DECLARE @Code VARCHAR(20)
DECLARE @ZPID INT
DECLARE @Sql nvarchar(max)
DECLARE @Con nvarchar(max)
DECLARE @ConStr nvarchar(max)

WHILE (Select Count(*) From Codes with(nolock) where Proceed =0 ) > 0
BEGIN
---select * from gen
SELECT Top 1 @ZPID=ZPLID, @Code=Code  From Codes with(nolock) where Proceed=0 
SELECT * INTO Condition FROM Gen G with(nolock) WHERE g.Code=@Code AND G.ZPLID=@ZPID AND G.ZfeatureKey IS NOT NULL

SET @Con=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')')   
FROM Condition CC  INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')






SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And FeatureValue ' ) , CAST(EStrat AS NVARCHAR(2500)),')')   --ValueName
FROM Condition CC  INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'')

    SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr )


 DECLARE @separator CHAR(1) = '$';
SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
stuff(( SELECT  ''$'' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()]
                    FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from Condition C 
inner join dbo.core_datadefinition_Detailes d with(nolock) on C.ZfeatureKey=d.columnnumber
INNER JOIN PartAttributes P on P.partid=PM.partid)CP
where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
ORDER BY CP.ZfeatureKey

                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  1, '''') as FeatureName,
stuff(( SELECT  ''$'' + CAST( CP2.FeatureValue AS VARCHAR(300)) AS [text()]
                    FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2
INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey)CP2
where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code
ORDER BY CP2.ZfeatureKey
                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  1, '''') as FeatureValue
FROM 
PartAttributes PM 
INNER JOINCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',
'Where (1=1 and  ',@Con ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition))

EXEC (@SQL)
DROP TABLE Condition

UPDATE Codes Set Proceed = 1 Where @ZPID=ZPLID AND Code=@Code

 END
Posted
Updated 19-Nov-21 19:20pm

GROUP BY and DISTINCT do not do the same thing: GROUP BY is for aggregate values, not just "removing duplicates"

SO When you use GROUP BY the only things you can return from the SELECT are the column(s) referenced in the GROUP BY clause, and the values from Aggregate functions: SUM, AVERAGE, and so on. YOu cannot return any "normal column content" unless it is explicitly listed in the GROUP BY - and each time you add a column to the grouping, you increase the number of rows returned, not reduce it.

The two operations are not interchangeable; they are very different functions that just seem to return similar results.
Have a look here, it may help: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
   
Re Advice on Indexes - you confirmed that my suggestion resolved your earlier question What I do to enhance this statement select into ?[^]
For more general advice see this CP article Database performance optimization part 1 (Indexing strategies)[^] or this one Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
   
Without knowing more information about the bottleneck just based on the SQL statements, you could try playing with following indexes

Table: Codes 
- Index 1: Proceed
- Index 2: ZPLID, Code

Table: Gen
- Index 1: Code, ZPLID, ZfeatureKey

Table: Condition 
- Index 1: ZfeatureKey

Table: Allfeatures 
- Index 1: IsNumericValues, ZfeatureKey

Table: core_datadefinition_Detailes 
- Index 1: ColumnNumber

Table: PartAttributes 
- Index 1: PartId
- Index 2: ZfeatureKey
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900