CODEWITHSUNDEEP
sqldb2db2-luw
Daveo
Daveo

Reputation: 19872

Order by best match then by name

I want a SQL like query and have results that start with the input paramater ordered first and the remainder of result will be sorted alphabetically.

So if I have

Foobbb
aaafoo
Fooaaa
bFoob
cccfooccc

and I search for foo I want it sorted as:

Fooaaa
Foobbb
aaafoo
bFoob
cccfooccc

so that me most releivant items are first. Want it a clear to understand and good perofmance way to do this? I could create a temp table but thought it too much overhead.

I tried

DECLARE cemployer CURSOR
WITH RETURN
FOR SELECT employer
    FROM   ((SELECT employer_name,  1 AS grp
             FROM   employer e
             WHERE  Upper(employer_name) LIKE Upper(i_employer|| '%'))
            UNION
            (SELECT employer_name, 2   AS grp
             FROM   employer e
             WHERE  Upper(employer_name) LIKE Upper('%'||i_employer|| '%')))
    ORDER  BY grp,
              employer;

OPEN cemployer;

However when I do this i get

Fooaaa
Foobbb

repeated again at the bottom of the result set. DB2 will not allow me to put a distinct on the outter query. I know i could solve this problem a number of ways by doing substring or locate or other string functions but wanted to know the most elegant way to do this and ensure no duplictes are returned.

Upvotes: 1

Views: 2209

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The problem is that an emplyer matches both groups, and you only want the first match. One way to fix this is by changing the query to a group by:

SELECT employer
FROM   ((SELECT employer_name,  1 AS grp
         FROM   employer e
         WHERE  Upper(employer_name) LIKE Upper(i_employer|| '%')
        )  UNION
        (SELECT employer_name, 2   AS grp
         FROM   employer e
         WHERE  Upper(employer_name) LIKE Upper('%'||i_employer|| '%')
        )
       )
group by employer
ORDER  BY min(grp), employer;

This is a pretty easy way to fix it, without fiddling with the logic that defines each group.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

General method

ORDER BY CASE WHEN col like 'foo%' THEN 1 ELSE 2 END,col

Upvotes: 7

Related Questions