April 20, 2011 at 11:34 pm
Hi Friends,
i am having a table named "Tb_studentDetails"
the create syntax is as follows
CREATE TABLE [dbo].[tb_studentDetails](
[Adm_no] [varchar](50) NULL,
[first_name] [varchar](50) NULL,
[mid_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[father_name] [varchar](500) NULL,
[session] [varchar](50) NULL,
[class] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
now
insert the following data in this table...
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2036', N'ESHITA', N' ', N'SINGH', N' JITENDER SINGH', N'2010 - 2011', N'III(A)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2036', N'Rajeev', N'', N'Solanki', N' JITENDER SINGH', N'2011 - 2012', N'IV(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2037', N'DIVYAM', N'', N'TEOTIA', N' SUDHIR TEOTIA', N'2011 - 2012', N'IV(D)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2037', N'Divyam', N' ', N'Teotia', N' Sudhir Teotia', N'2010 - 2011', N'III(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2039', N'Rhythem', N' ', N'Saxena', N' Ashish Saxena', N'2010 - 2011', N'III(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2039', N'RHYTHEM', N'', N'SAXENA', N' ASHISH SAXENA', N'2011 - 2012', N'IV(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2040', N'ISHITA', N'', N'CHANDRA', N' RAJIT CHANDRA', N'2011 - 2012', N'IV(D)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2040', N'Mohan', N' ', N'Gupta', N' Rajit Chandra', N'2010 - 2011', N'III(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2041', N'RADHIKA', N' ', N'VERMA', N' VIKAS KUMAR VERMA', N'2010 - 2011', N'III(A)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2041', N'Ritika', N'', N'VERMA', N' VIKAS KUMAR VERMA', N'2011 - 2012', N'IV(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2042', N'AKSHIT', N'', N'GUPTA', N' KAPIL GUPTA', N'2011 - 2012', N'IV(D)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2042', N'Ajay', N' ', N'GUPTA', N' KAPIL GUPTA', N'2010 - 2011', N'III(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2043', N'Hansika', N' ', N'Chhabra', N' Anurag Chhabra', N'2010 - 2011', N'III(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2043', N'HANSIKA', N'', N'CHHABRA', N' ANURAG CHHABRA', N'2011 - 2012', N'IV(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2044', N'AASHI', N'', N'', N' ASHWANI SHARMA', N'2011 - 2012', N'IV(C)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2044', N'', N' ', N'AASHI', N' ASHWANI SHARMA', N'2010 - 2011', N'III(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2045', N'SAMRIDHI', N' ', N'SETHI', N' BHARAT RATTAN SETHI', N'2010 - 2011', N'III(A)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2045', N'Sarika', N'', N'Mishra', N' BHARAT RATTAN SETHI', N'2011 - 2012', N'IV(B)')
INSERT [dbo].[tb_studentDetails] ([Adm_no], [first_name], [mid_name], [last_name], [father_name], [session], [class]) VALUES (N'06/2047', N'VANSHIKA', N'', N'SINGH', N' VINAY SINGH', N'2011 - 2012', N'IV(D)')
now in above data..
there are two row with same admission no 06/2036 and with name Eshita and Rajeev..
similarly admission no 06/2040 is duplicate with name Ishita and Mohan
similarly admission no 06/2041 with name Radhika and Ritika
and so on
this means i need only those adm_no that exist with different different name excluding same name with same adm_no
i neet to generate a select query that returns these type of row
like
adm_no first name
06/2036 Eshita
06/2036 Rajeev
06/2040 Ishita
06/2040 Mohan
06/2041 Radhika
06/2041 Ritika
this means i need that row that are with same admission no but with different first name.
now let me describe more
for example we have a table with two column adm_no and name
and inserted data is
adm_no first name
06/2036 Eshita
06/2036 Rajeev
06/2067 vijay
06/2067 vijay
06/2040 Ishita
06/2040 Mohan
06/2041 Radhika
06/2041 Ritika
06/2043 Ram
06/2043 Ram
so i neet to select only those adm_no that are exist with different different name but with same adm_no output should be:
adm_no first name
06/2036 Eshita
06/2036 Rajeev
06/2040 Ishita
06/2040 Mohan
06/2041 Radhika
06/2041 Ritika
now i think it is suficient ...
thanks !!
April 20, 2011 at 11:46 pm
Will this help you?
SELECT * FROM
(
SELECT * , COUNT_DIST = COUNT(*) OVER(PARTITION BY ADM_NO , FIRST_NAME)
FROM [tb_studentDetails]
) SUB_Q
WHERE COUNT_DIST = 1
April 20, 2011 at 11:53 pm
Cross Post.. Please direct your replies to this http://www.sqlservercentral.com/Forums/Topic1096236-149-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply