UNIQUE and LATEST record...

Maybe sometimes you need to extract the unique and latest records from a table. Kung ako, here's one way para ma-resolve ko ito.

Let's assume the below table structure. The field id is unique in this case while phonenumber has duplicate values.

--sample table structure

CREATE TABLE [dbo].[mytable](

[id] [int] NOT NULL,

[phonenumber] [varchar](50) NOT NULL,

[customername] [varchar](50) NOT NULL

) ON [PRIMARY]

--insert some dupe values

insert into mytable (id,phonenumber,customername)

select 1,'1111111111','John Smith'

insert into mytable (id,phonenumber,customername)

select 2,'1111111111','John Smith'

insert into mytable (id,phonenumber,customername)

select 3,'1111111111','John Smith'

insert into mytable (id,phonenumber,customername)

select 4,'2222222222','Juan Dela Cruz'

insert into mytable (id,phonenumber,customername)

select 5,'3333333333','Pedro Penduko'

insert into mytable (id,phonenumber,customername)

select 6,'4444444444','Jose Ponce'

insert into mytable (id,phonenumber,customername)

select 7,'4444444444','Jose Ponce'

insert into mytable (id,phonenumber,customername)

select 8,'1111111111','John Smith'

--SQL SERVER 2000

select * from mytable

select *

from mytable A

where id in

( select max(id)

from mytable

where phonenumber = A.phonenumber ) order by id

--QUERY 1 OUTPUT:

id phonenumber customername

1 1111111111 John Smith

2 1111111111 John Smith

3 1111111111 John Smith

4 2222222222 Juan Dela Cruz

5 3333333333 Pedro Penduko

6 4444444444 Jose Ponce

7 4444444444 Jose Ponce

8 1111111111 John Smith

--QUERY 2 OUTPUT:

id phonenumber customername

4 2222222222 Juan Dela Cruz

5 3333333333 Pedro Penduko

7 4444444444 Jose Ponce

8 1111111111 John Smith

--IN SQL SERVER 2005

WITH latest AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY phonenumber ORDER BY id desc) As num

, id

, phonenumber

, customername

from (select id,phonenumber,customername from mytable) as R

)

SELECT

id

,phonenumber

, customername

FROM latest

WHERE num = 1 order by id

--QUERY 3 OUTPUT:

id phonenumber customername

4 2222222222 Juan Dela Cruz

5 3333333333 Pedro Penduko

7 4444444444 Jose Ponce

8 1111111111 John Smith