Distinct Command - 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

Distinct Command

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-10 : 06:11:33

Hi,

I have the following select statement which works fine.

SELECT Distinct TOP (100) PERCENT t3.pat_eid AS ImportPat_eids, t2.prac_no, t2.prac_eid AS GPRDTechprac_eid, t1.o_pat_eid, t1.o_prac_uid, t1.o_pat_id, t1.o_pat_birth_yr, t1.o_pat_birth_mth, t1.o_pat_curr_gender
FROM  GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
    ODSData5.dbo.o_pat AS t1 ON t2.prac_eid = t1.o_prac_uid INNER JOIN
    dbo.tblImport AS t3 ON t1.o_pat_eid = t3.pat_eid
ORDER BY ImportPat_eids

The problem is the t1.o_pat_birth_mth can be either NULL or a value e.g 2, 3 , 4 etc.

Therefore the above select statement pulls two records the one with NULL and the second record with the value. However, what I want to do is bring ONLY one record instead. Therefore if there are 2 records display the one with a value. However, if both records have values rather than NULL then take the top record instead.

Any help pls

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-10 : 06:28:06

[code]SELECT t3.pat_eid AS ImportPat_eids, t2.prac_no, t2.prac_eid AS GPRDTechprac_eid, t1.o_pat_eid, t1.o_prac_uid, t1.o_pat_id, t1.o_pat_birth_yr, max(t1.o_pat_birth_mth) as o_pat_birth_mth, t1.o_pat_curr_gender
FROM GPRDTech.gprdsql.TblPracDetails AS t2 INNER JOIN
ODSData5.dbo.o_pat AS t1 ON t2.prac_eid = t1.o_prac_uid INNER JOIN
dbo.tblImport AS t3 ON t1.o_pat_eid = t3.pat_eid
GROUP BY t3.pat_eid, t2.prac_no, t2.prac_eid AS GPRDTechprac_eid, t1.o_pat_eid, t1.o_prac_uid, t1.o_pat_id, t1.o_pat_birth_yr,t1.o_pat_curr_gender
ORDER BY ImportPat_eids
[/code]

Madhivanan

Failing to plan is Planning to fail

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-10 : 07:18:46

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources