return first matching record only

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

SQL Server 2000 Forums

Transact-SQL (2000)

return first matching record only

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-01 : 16:25:40

Hi friends
i have data like this
readcode name termkey
-------- ------------------------------ ----------
0.... Occupations OCCUPATION
2.... Examination / Signs SIGNS
3.... Diagnostic procedures DIAGNOSTIC
3.... Diagnostic procedures PROCEDURES
4.... Laboratory procedures LABORATORY
4.... Laboratory procedures PROCEDURE
4.... Laboratory procedures INVESTIGAT
4.... Laboratory procedures TEST
5.... Radiology/physics in medicine
5.... Radiology/physics in medicine PHYSICS

and i want to write query that returns first matching record only

readcode name termkey
-------- ------------------------------ ----------
0.... Occupations OCCUPATION
2.... Examination / Signs SIGNS
3.... Diagnostic procedures DIAGNOSTIC
4.... Laboratory procedures LABORATORY
5.... Radiology/physics in medicine

how can i write that query please.Thanks for your help

Cheers

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-01 : 16:42:59

You didn't define "matching record" but assuming it means for each readcode display only one name and Turnkey.
Try this:


create table #junk (readcode int, name varchar(30), termkey varchar(30))
insert #junk
select 0, 'Occupations', 'OCCUPATION' union all
select 2, 'Examination/Signs', 'SIGNS' union all
select 3, 'Diagnostic procedures', 'DIAGNOSTIC' union all
select 3, 'Diagnostic procedures', 'PROCEDURES'


select	readcode
	,name
	,termkey
from	#junk a
where	name + termkey in
	(
	select top 1 name + termkey
	from	#junk b
	where	readcode = a.readcode
	order by name+termkey
	)

drop table #junk

Output:
readcode    name                           termkey                        
----------- ------------------------------ ------------------------------ 
0           Occupations                    OCCUPATION
2           Examination/Signs              SIGNS
3           Diagnostic procedures          DIAGNOSTIC

Be One with the Optimizer
TG

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-01 : 16:58:26

>> You didn't define "matching record" but assuming it means for each readcode display only one name and Turnkey.

Yes TG, thats what i meant.
BTW ur code works nicely. Thanks for ur help

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-01 : 17:01:02

BTW i tried another way like below. this table also has a PK field which i used here
select readcode
,name
,termkey
from mytable where pkfld in
(select min(pkfld) from mytable group by readcode,name)

this also works fine :)

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-02 : 02:30:02

Also try

select readcode, name, termkey from yourTable T
where termkey=(select top 1 termkey from yourTable where readcode=T.readcode)

Madhivanan

Failing to plan is Planning to fail

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-03-02 : 14:47:21

thats cool Madhivanan. Thanks for that.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources