Dynamic Point System for Ordering

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

SQL Server Development (2000)

Dynamic Point System for Ordering

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-01-07 : 18:28:16

Hey, guys. I need to write a search query that weights the order on which the records are displayed based on 3 search criteria that they enter. Some of the options may be required by the user in which case they would get 2 points and some may simply be a preference in which case they would get 1 point. Anyone have any idea of how to tackle this?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-08 : 00:29:11

try to use UNION:

select 1 as Priority, ... from t where foo1 & foo2 & foo3
UNION
select 2 as Priority, ... from t where foo1 & foo2
UNION
select 3 as Priority, ... from t where foo1
ORDER BY Priority

derketo
Starting Member

28 Posts

Posted - 2005-01-09 : 00:58:31

Thanks, Stoad. That never occurred to me. I was about to break out the dreaded cursors.

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-09 : 09:38:53

You could also try, e.g.:

SELECT
ColA,
ColB,
ColC,
...
FROM YourTable
ORDER BY
CASE
WHEN foo1 & foo2 & foo3 THEN 1
WHEN foo1 & foo2 THEN 2
WHEN foo1 THEN 3
END ASC

... Test both on your end and see which performs better...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources