unable to get distinct list - 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

unable to get distinct list

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-02 : 09:41:45

Hello everyone,

I am using the below script to gain a list but only having the maximum valid_from date and distinct
individual_ref's

(select max(attribute.valid_from), (individual.individual_ref), attribute.attr_code_ref, lookup_full_desc, attribute.valid_from
from individual, member, attribute, [lookup]
where attribute.valid_from >=(SELECT DATEADD(month,datediff(month,0,getdate())-1,-0))
--and (SELECT DATEADD(month,datediff(month,-1,getdate())-0,-1))
and member.individual_ref = individual.individual_ref
and member.individual_ref = attribute.individual_ref
and attribute.attr_code_ref = lookup.lookup_ref
and member.member_status = 33
and attribute.attr_code_ref in (3506, 2062, 3650,2709,3648)
and member.member_class = 1264
and member.member_grade not in( 60,61,62)
group by individual.individual_ref, attribute.attr_code_ref, lookup_full_desc, attribute.valid_from)

is there a way i get only view the maximum valid_to dates on duplicate individual_ref's.???

Kind Regards

Rob

MCTS / MCITP certified

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-02 : 09:59:09

Whar results are you currently getting with this query?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-02 : 10:05:03

As you can see below. I am getting duplicate Ref numbers with different dates
I want to have a distinct list of the Ref number, but choosing the maximum valid_from date.

Ref Code Description Valid_From Date

38831 3648 Associate to New Associate 2011-08-04 00:00:00.000
38907 3648 Associate to New Associate 2011-08-16 00:00:00.000
39124 3506 Associate to Certified 2011-08-05 16:33:00.000
39124 3506 Associate to Certified 2011-08-19 14:05:00.000
39124 3650 Associate to Affiliate 2011-08-03 00:00:00.000
39410 3648 Associate to New Associate 2011-08-17 00:00:00.000
39525 3648 Associate to New Associate 2011-08-04 00:00:00.000

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-02 : 10:17:44

Hi

Not sure but can you not use distinct with top 10 operator? like this

select top 10 T.Date
from (select distinct ref, date from table) AS T

Not sure if this is right what I am advising it is hard as I am new to SQL too and would need to know the data and relations etc.

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-02 : 10:44:29

Just checked I don't think that top 10 would work

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-09-02 : 10:54:35

ok thank you for replying so quickly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources