The following query is used to search for a name in the database. The name is dynamically entered via a web form. Each name can have multiple ways of spelling or ending so it does a like search, but I want the result to only return one matching value.

EXAMPLE..

search for abc can return
- abc limited
- abc lim
- abc lim.


SELECT r.Id, r.IM_OrgId, im.Name, r.BK_OrgId, bk.Name
FROM Rule r
INNER JOIN OrgName im ON im.Org_Id = r.IM_OrgId
INNER JOIN OrgName bk ON bk.Org_Id = r.BK_OrgId AND bk.isDefault = 1
WHERE im.Name LIKE UPPER('abc%')

The isdefault flag limits the bk.Name to only a single result on the join, but im.name can have many values returned...

RESULTS

123, 321.23, abc limited, 342, Test
123, 321.23, abc lim, 342, Test
123, 321.23, abc lim., 342, Test
432, 324.23, abc 123, 432, Test2

The above results need to only list the 2 ids, 123, 432....which im.name that gets displayed does not matter.


Any help would be great..

Try adding 'fetch first 1 row only' to the end of your query.

http://dbforums.com/showthread.php?threadid=921672

This post should give you a clue


Cheers
Sathyaram

I tried playing with these examples but nothing seemed to work (i was never able to filter out duplicate aliases)....any other ideas?

I think what Sathyaram was trying to get you to do was to look at the row_number() function to do what you want. Try something like:

with t1 (id, rownum, im_orgid,IM_name,bk_ordid,BK_NAME) as
(SELECT r.Id, row_number() over (partition by r.id),r.IM_OrgId, im.Name, r.BK_OrgId, bk.Name
FROM Rule r
INNER JOIN OrgName im ON im.Org_Id = r.IM_OrgId
INNER JOIN OrgName bk ON bk.Org_Id = r.BK_OrgId AND bk.isDefault = 1
WHERE im.Name LIKE UPPER('abc%')) select id,im_orgid,IM_name,bk_ordid,BK_NAME from t1 where rownum = 1

HTH

Andy