Distinct Values

Hi

If value is 21000030 it should return 4 not 5 . In first record 21000030 exists 2 times

21000032 then it should return 3 not 4.

USE [Live] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[OCRD]( [CardCode] nvarchar NOT NULL, [Emp_1] nvarchar NULL, [Emp_2] nvarchar NULL, [Emp_3] nvarchar NULL, CONSTRAINT [OCRD_PRIMARY] PRIMARY KEY CLUSTERED ( [CardCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO SET ANSI_PADDING OFF GO

INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C01',N'21000030', N'21000030', NULL) INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C02',N'21000032', N'21000030', NULL) INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C03', N'21000038', N'21000032', NULL) INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C04', N'21000040', N'21000033', N'21000030') INSERT [dbo].[OCRD] ([CardCode],[Emp_1], [emp_2], [Emp_3]) Values (N'C05', N'21000030', N'21000032', N'21000032')

Thanks

What is your question actually ?

Please explain what you want to achieve together with some sample data and the exepected result. And also include your existing query attempt. Please also format your question or query using </> button

PS. You need to define the size of the nvarchar column when creating the table or it will be assumed as 1