Sql Srever 2008 problem.

Posted by Rajanarayanan under Sql Server on 5/10/2012 | Points: 10 | Views : 1826 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

I have doubt in sql server 2008 .

1. Duplicate records remove problem.

ID Name Category

1 AAAA Male

2 BBBB Female

1 AAAA Male

1 AAAA Female

2 BBBB Female

2 BBB12 Female

In above table there is no Identity Column.

How to remove the duplicate values in single query ....

Output :

Like this;

1 AAAA Male

2 BBBB Female

1 AAAA Female

2 BBB12 Female..

2. Top

I have using Top statement in sql server 2008.

If i have using SELECT TOP(1) Column name from Table Name

it displays Single value .

suppose if i have using SELECT TOP(5) Column name from Table Name

it displays Five Records .

I want to get 5th record only using TOP statement . Is it possible in sql server 2008.

Thanks & Regards
Narayanan P

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 5/10/2012 [Member] [MVP] Silver | Points: 25

0	Hi 1. To eliminate the Duplicate data: Select Name, Category from ( Select ROW_NUMBER() Over(Partition by Id, Name, Category Order by Id, Name, Category) [Rept],* from <TableName> ) as Sub Where Rept = 1 Go Name Category ---- --------- AAAA Female AAAA Male BBB12 Female BBBB Female (Or) 2. To eliminate the Duplicate and fetch the data as you want and Retain the physical order of the record Select Identity(Int,1,1) Seq,* Into #Test1 from <TableName> Select Name, Category from ( Select ROW_NUMBER() Over(Partition by Id, Name, Category Order by Id, Name, Category) [Rept],* from #Test1 ) as Sub Where Rept = 1 Order by Seq Go Drop Table #Test1 Go Name Category ----- --------- AAAA Male BBBB Female AAAA Female BBB12 Female 3. To fetch Nth record: Select ID,Name, Category from ( Select ROW_NUMBER() Over(Order by Id,Name,Category) [Nth],* from <TableName> )as Sub Where Nth = 5 Go Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Rajanarayanan, if this helps please login to Mark As Answer. \| Alert Moderator

Hi

1. To eliminate the Duplicate data:

Select Name, Category from

(

Select ROW_NUMBER() Over(Partition by Id, Name, Category Order by Id, Name, Category) [Rept],* from <TableName>

) as Sub

Where Rept = 1

Go

Name	Category

----	---------

AAAA	Female

AAAA	Male

BBB12	Female

BBBB	Female

(Or)

2. To eliminate the Duplicate and fetch the data as you want and Retain the physical order of the record

Select Identity(Int,1,1) Seq,* Into #Test1 from <TableName>



Select Name, Category from

(

Select ROW_NUMBER() Over(Partition by Id, Name, Category Order by Id, Name, Category) [Rept],* from #Test1

) as Sub

Where Rept = 1

Order by Seq

Go



Drop Table #Test1

Go

Name	Category

-----	---------

AAAA	Male

BBBB	Female

AAAA	Female

BBB12	Female

3. To fetch Nth record:

Select ID,Name, Category from

(

Select ROW_NUMBER() Over(Order by Id,Name,Category) [Nth],* from <TableName>

)as Sub

Where Nth = 5

Go

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajanarayanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajanarayanan on: 5/10/2012 [Member] Starter | Points: 25

0	Thanks for your reply. It is not delete the duplicate data. First one is using for eliminate the Duplicate data. Second one : The user can't have the permission for creating the new table(#Test1). Thanks & Regards Narayanan P Rajanarayanan, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts