April 20, 2011 at 5:35 am
Hi Everyone,
i am having a table like
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
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)')
in this data will be in following form:
Adm_no FirstName Last_name Session Class
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/743 ANSHIKA GUPTA 2011 - 2012 IX
01/743 Anshika Gupta 2010 - 2011 VIII
01/745 Tunisha Jindal 2010 - 2011 VIII
01/745 TUNISHA JINDAL 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/759 YAWAR KHAN 2011 - 2012 IX
01/759 YAWAR KHAN 2010 - 2011 VIII
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
Now
i want a select statement that retrive duplicate adm_no
and returns followng output :
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
so please send select statment
thanks!!
April 20, 2011 at 6:56 am
To help those who want to help you, please, please post your table definitions, sample data, and your expected/required results in an easy to use format. To do this please click on the first link in my signature block. The article which will display, contains sample T-SQL statements to make posting your table definition and sample data very easy and quick to do.
April 20, 2011 at 7:36 am
ok sorry friends,
now i am sending script of table definition with data
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
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)')
in this data will be in following form:
Adm_no FirstName Last_name Session Class
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/743 ANSHIKA GUPTA 2011 - 2012 IX
01/743 Anshika Gupta 2010 - 2011 VIII
01/745 Tunisha Jindal 2010 - 2011 VIII
01/745 TUNISHA JINDAL 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/759 YAWAR KHAN 2011 - 2012 IX
01/759 YAWAR KHAN 2010 - 2011 VIII
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
Now
i want a select statement that retrive duplicate adm_no
and returns followng output :
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
so please send select statment
April 20, 2011 at 7:53 am
It is unclear what you want. You posted the details of the data you want from this table but...that data does not exist in the sample data. Maybe if you can be clear about the desired output based on the actual sample data we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2011 at 7:58 am
shubham.saxena67 (4/20/2011)
...in this data will be in following form:Adm_no FirstName Last_name Session Class
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/743 ANSHIKA GUPTA 2011 - 2012 IX
01/743 Anshika Gupta 2010 - 2011 VIII
01/745 Tunisha Jindal 2010 - 2011 VIII
01/745 TUNISHA JINDAL 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/759 YAWAR KHAN 2011 - 2012 IX
01/759 YAWAR KHAN 2010 - 2011 VIII
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
Now
i want a select statement that retrive duplicate adm_no
and returns followng output :
01/742 NIKHIL SINGH 2010 - 2011 VIII
01/742 ASHISH SINGH 2011 - 2012 IX
01/746 Abhinav Bansal 2011 - 2012 X
01/746 Rajeev Bansal 2010 - 2011 IX
01/760 Prashant Vasudev 2010 - 2011 VIII
01/760 Devesh Gupta 2011 - 2012 IX
ALL of the rows in the first table are dupes on adm_no. What else identifies the rows in the second table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 8:42 am
shubham.saxena67
with cte
as (select row_number() over(partition by Adm_no order by adm_no) as rn,Adm_no,
first_name,mid_name,last_name
from dbo.tb_studentDetails)
SELECT * FROM cte
Sample of result returned:
rn Adm_no first_name mid_name
1 06/2036 ESHITA SINGH
2 06/2036 Rajeev Solanki
1 06/2037 DIVYAM TEOTIA
2 06/2037 Divyam Teotia
1 06/2039 Rhythem Saxena
2 06/2039 RHYTHEM SAXENA
1 06/2040 ISHITA CHANDRA
2 06/2040 Mohan Gupta
Code removed per correction of me by ColdCoffee - and thanks
April 20, 2011 at 11:30 pm
Hi Friend,
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 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
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:51 pm
bitbucket-25253 (4/20/2011)
Now let me admonish you once more. You obviously did NOT test your detail information before posting. Other wise you would have discovered that
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',does NOT insert values into a table. It needs to be
INSERT INTO [dbo].[tb_studentDetails]
[/code]
Ron, if i am not wrong, INSERT <TableName> (Cols) VALUES (v1, v2) = INSERT INTO <TableName> (Cols) VALUES (v1, v2) . it's another variant of INSERT statment.
April 20, 2011 at 11:53 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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply