sql server msde: Hi there, i have searched for an answer to my question but can't find
one anywhere so far, hopefully this is the right place to post such a
question, thanks in advance for any help you can give me!

I've just moved our company DB from access to msde and while its gone
fairly smoothly I have one problem i'm unable to solve. We have a
plasma screen in our office which shows leads coming into the business,
theres a simple ajax updater to keep it accurate up to 30 secs. We use
a set of rules to prioritise and order these leads.

Under jet this was easy as you can put expressions in the order by
clause in this style:

SELECT col1 FROM Table WHERE blahblah = 1 ORDER BY col2 = 3, col4 < 10

msde is giving me errors whenever i try anything like this, can anyone
suggest a way I can get this working or another way I can achieve the
same goal?

Thanks again and many apologies if this is a stupid question!

hi,
[quoted text, click to view]

ORDER BY col2 = 3, col4 < 10
can be replaced by boolean check via CASE syntax..

SET NOCOUNT ON;
CREATE TABLE #t (
Col1 varchar(5),
Col2 int,
Col3 int,
Col4 int
);

INSERT INTO #t VALUES ( 'aa', 5 , 6 , 11 );
INSERT INTO #t VALUES ( 'mm', 6 , 6 , 8 );
INSERT INTO #t VALUES ( 'zz', 5 , 6 , 5 );
INSERT INTO #t VALUES ( 'gg', 5 , 5 , 9 );
GO

SELECT t.Col1
FROM #t t
--WHERE ...
ORDER BY CASE WHEN t.Col2 = t.Col3 THEN 0 ELSE 1 END,
CASE WHEN t.Col4 < 10 THEN 0 ELSE 1 END;
GO
DROP TABLE #t;
--<----------
Col1
-----
mm
gg
zz
aa

the first CASE checks for t.Col2 = t.Col3 , resultin in 0 or 1 depending on
the result... 0 sorts before 1, so you get the desired result.. the same for
the second boolean check expression..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply