Sql Srever 2008 problem.

Posted by Rajanarayanan under Sql Server on 5/10/2012 | Points: 10 | Views : 887 | Status : [Member] | Replies : 2
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response