Urgent!! Select Distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent!! Select Distinct

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-23 : 03:55:50

Hi Everyone,
I need to query data urgent.
Can anyone help in this??

below is my example table.

NRIC Answer Address Username Submit_DAte
--------------------------------------------------------
C123 c USA, north David 1/1/2008
C123 d USA, north2 David2 2/2/2008
C123 e USA, north3 David3 3/3/2008
C234 c USA, East Peter 4/4/2008

how to i select and display all data
distinct only by NRIC and only the 1st sumbit date will selected.

Example result that i want as below :
NRIC Answer Address Username Submit_DAte
--------------------------------------------------------
C123 c USA, north David 1/1/2008
C234 c USA, East Peter 4/4/2008

Can anyone help in this??

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-23 : 04:06:30

If you are using sql 2005 try this

select t.NRIC, t.Answer, t.Address, t.Username, t.Submit_DAte
from
(
select row_number over( partition by NRIC order by Submit_DAte ) as sno ,NRIC, Answer, Address, Username, Submit_DAte
from your table ) t
where t.sno = 1

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-23 : 04:13:22

select * from
( select *,rank() over (partition by nric order by submit_date ) as rn from nric ) as rn
where rn = 1

I Struggle For Excellence

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-23 : 04:18:21

i tried, but i found this error:

Error in SELECT clause: expression near 'OVER'.
Missing FROM clause.
Unable to parse query text.

Incorrect syntax near the keyword 'OVER'

quote:
Originally posted by raky

If you are using sql 2005 try this

select t.NRIC, t.Answer, t.Address, t.Username, t.Submit_DAte
from
(
select row_number over( partition by NRIC order by Submit_DAte ) as sno ,NRIC, Answer, Address, Username, Submit_DAte
from your table ) t
where t.sno = 1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 04:21:07

quote:
Originally posted by raky

If you are using sql 2005 try this

select t.NRIC, t.Answer, t.Address, t.Username, t.Submit_DAte
from
(
select row_number() over( partition by NRIC order by Submit_DAte ) as sno ,NRIC, Answer, Address, Username, Submit_DAte
from your table ) t
where t.sno = 1

he missed the brackets now try this

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-23 : 04:24:59

Use Row_number(),Rank() or Dense_rank(), It will gives ur Expected Output

I Struggle For Excellence

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-23 : 04:29:42

If you are using sql 2000 then use this

select t1.NRIC, t1.Answer, t1.Address, t1.Username, t1.Submit_DAte
from yourtable t1
inner join ( select nric, min(Submit_DAte) as Date from yourtable group by nric ) t2 on t2.nric = t1.nric and t2.date = t1.Submit_DAte

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-23 : 04:34:20

Thanks bklr,

It work now.

quote:
Originally posted by bklr

quote:
Originally posted by raky

If you are using sql 2005 try this

select t.NRIC, t.Answer, t.Address, t.Username, t.Submit_DAte
from
(
select row_number() over( partition by NRIC order by Submit_DAte ) as sno ,NRIC, Answer, Address, Username, Submit_DAte
from your table ) t
where t.sno = 1

he missed the brackets now try this

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2008-12-23 : 04:35:02

Thanks Nagewwar9,

Your query work too. :)

quote:
Originally posted by Nageswar9

Use Row_number(),Rank() or Dense_rank(), It will gives ur Expected Output

I Struggle For Excellence

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-23 : 04:38:48

WelCome

I Struggle For Excellence

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 04:42:42

SELECT x.* FROM
(SELECT MIN(submit_date) AS date FROM yourtable GROUP BY nric ) d
CROSS APPLY (SELECT * FROM yourtable WHERE submit_date = d.date) x

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 04:43:14

SELECT x.* FROM
(SELECT MIN(submit_date) AS date FROM yourtable GROUP BY nric ) d
CROSS APPLY (SELECT * FROM yourtable WHERE submit_date = d.date) x

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 04:55:25

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources