Search

Custom Search

Saturday, March 8, 2008

Ordering a record according to search followed by the rest of the record

When searching from grid, commonly the searched record is left on the grid removing all unsearch record. Sometimes we don't want that style to happen. We want to put the searched item on top of the grid and the rest below it.
Here is a trick to do that:

SELECT *, '0'+ SearchField as OrderBy FROM Tables WHERE SearchField like '[value]%'
UNION ALL
SELECT *, '1'+ SearchField as OrderBy FROM Tables WHERE SearchField not like '[value]%' Order By OrderBy

OR

SELECT field1, field2, SearchField,CASE WHEN SearchField like '[Value]%' THEN '0' ELSE '1' END ORDER BY 4,SearchField

No comments:

Post a Comment

Adsense Banner