|
|
Posted by Bill on 10/18/06 18:48
You can try it - It looks like it could be valid.
If you have not worked out the ORDER BY - tip: Use ORDER BY 1 lest you
have to repeat the case statement within the order by clause
Generally, I stay away from CASE statements whenever there is another
way ( for performance reason )
Ed Murphy wrote:
> Bill wrote:
>
> > Lionel Pinkhard wrote:
>
> >> I currently have a select query with "Description = 'input from user
> >> here'" which basically returns the associated row containing the
> >> Description field exactly as typed by the user, however, would it be
> >> possible to, if not found, return the closest match? I am using
> >> Microsoft Visual Studio 2005 with C# as language and Microsoft SQL
> >> Server 2005 as database.
>
> > I am making an assumption that you can return multiple rows to the user
> >
> > to pick from. And that you want the exact match to appear as the most
> > relevant and first in the list.
> >
> >
> > SELECT 1 AS sort_order, row_data
> > FROM tbl
> > WHERE Description = 'input'
> > UNION
> > SELECT 2 AS sort_order, row_data
> > FROM tbl
> > WHERE Description like '%input%'
> > AND Description != 'input'
> > ORDER BY sort_order, ?
>
> [top-posting fixed]
>
> Does this work? I forget whether ORDER BY accepts calculated columns.
>
> select
> case when Description = 'input' then 1
> when Description like '%input%' then 2
> else -1 -- can't happen
> end as sort_order,
> row_data
> from tbl
> where Description like '%input%'
> order by sort_order -- and optionally one or more additional columns
Navigation:
[Reply to this message]
|