RE: SQL custom Order by

2004-03-10 Thread Andrew Tyrone 
Try using CASE() :

SELECT
	*
FROM
	Table

ORDER BY
CASE catID
WHEN 10 THEN 1
WHEN 3 THEN 2
WHEN 5 THEN 3
WHEN 1 THEN 4
WHEN 7 THEN 5
WHEN 4 THEN 6
WHEN 9 THEN 7
WHEN 2 THEN 8
WHEN 6 THEN 9
WHEN 8 THEN 10
END

There might be an even more efficient way, but at least this relegates the
logic to one query.

Andy

-Original Message-
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by

I doubt this is possible, but is there a way to do a custom order by based
on the order I provide?I need the SQL results to be in a specific order
based on the catID, but not in ascending or descending order. The order
that I want them in is 10,3,5,1,7,4,9,2,6,8.

Example

Select *

>From table

Order by catID values(10,3,5,1,7,4,9,2,6,8)

I am using MS SQL 2000.

Thanks for the help.

Mark W. Breneman
-Cold Fusion Developer
-Network Administrator
Vivid Media
[EMAIL PROTECTED]
www.vividmedia.com
608.270.9770
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL custom Order by

2004-03-09 Thread Plunkett, Matt 
-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by

I doubt this is possible, but is there a way to do a custom order by
based 
 on the order I provide?I need the SQL results to be in a specific order 
 based on the catID, but not in ascending or descending order. The order 
 that I want them in is 10,3,5,1,7,4,9,2,6,8. 

 
(Not sure this is possible in Sql Server, cause I don't use that).

 
I would write a custom function to do this.So you would do:

 
select catID, mysort(catID) srt
 from foo
order by srt;

 
In the function, just return what order you want given the catId.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

Re: SQL custom Order by

2004-03-09 Thread Jochem van Dieten 
Mark W. Breneman said:
 I doubt this is possible, but is there a way to do a custom order by
 based on the order I provide?I need the SQL results to be in a
 specific order based on the catID, but not in ascending or
 descending order. The order that I want them in is
 10,3,5,1,7,4,9,2,6,8.

 Example

 Select *
 From table
 Order by catID values(10,3,5,1,7,4,9,2,6,8)

 I am using MS SQL 2000.

If MS SQL Server 2000 has a ListFind() function that operates similar
to the ListFind() function in CF it is pretty easy:

SELECT *
FROMtable
ORDER BY
 Cast(ListFind(CatID, '10,3,5,1,7,4,9,2,6,8') AS BOOLEAN),
 ListFind(CatID, '10,3,5,1,7,4,9,2,6,8')

If all CatID's are between 1 and 10 you can even leave the first order
condition out.

If MS SQL Server 2000 does not have such a function you might be able
to write a UDF for it or use an adapted Find() function.

Jochem
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL custom Order by

2004-03-09 Thread David Jones 
This could be a freak accident that this works for me.. But it did in SQL
2000.This may not be the most efficient way of doing this either.

SELECT*
FROMcategory
WHERE
 CatID = 10

union all

SELECT*
FROMcategory
WHERE
CatID = 3

union all

SELECT*
FROMcategory
WHERE
CatID = 5

union all

SELECT*
FROMcategory
WHERE
CatID = 1


Try it,

David
-Original Message-
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:39 PM
To: CF-Talk
Subject: RE: SQL custom Order by

-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by

I doubt this is possible, but is there a way to do a custom order by
based
 on the order I provide?I need the SQL results to be in a specific
order
 based on the catID, but not in ascending or descending order. The
order
 that I want them in is 10,3,5,1,7,4,9,2,6,8.

(Not sure this is possible in Sql Server, cause I don't use that).

I would write a custom function to do this.So you would do:

select catID, mysort(catID) srt
from foo
order by srt;

In the function, just return what order you want given the catId.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]

RE: SQL custom Order by

2004-03-09 Thread Mark A. Kruger - CFG 
David,

Yes but this may OR may not return them in the order you are looking for.
However, there IS an easy way to use the UNION operator this way (for
ordering). You need to stop using the asterisk (stop it! bad programmer!!).
Try this:

--
SELECTcol1, col2, col3, 1 AS orderCol
FROMcategory
WHERE
CatID = 10

UNION

SELECTcol1, col2, col3, 2 AS orderCol
FROMcategory
WHERE
CatID = 8

SELECTcol1, col2, col3, 3 AS orderCol
FROMcategory
WHERE
CatID = 9

UNION

SELECTcol1, col2, col3, 4 AS orderCol
FROMcategory
WHERE
CatID = 11

ORDER BY orderCol



Obviously you can do it in a loop. Here's a (completely untested) example:

Cfset Inx = 1
cfset thisList=10,8,9,11

Cfquery name=blah datasource=blah

cfloop list=#thisList# index=lItem
	SELECTcol1, col2, col3,#Inx# AS orderCol
	FROMcategory
	WHERE
	CatID = #lItem#

cfif Inx IS NOT listLen(thisList)
	UNION
/cfif
	cfset Inx = Inx + 1
 /cfloop
	ORDER BY orderCol
/cfquery

--

-Mark

-Original Message-
From: David Jones [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 2:36 PM
To: CF-Talk
Subject: RE: SQL custom Order by

This could be a freak accident that this works for me.. But it did in SQL
2000.This may not be the most efficient way of doing this either.

SELECT*
FROMcategory
WHERE
CatID = 10

union all

SELECT*
FROMcategory
WHERE
CatID = 3

union all

SELECT*
FROMcategory
WHERE
CatID = 5

union all

SELECT*
FROMcategory
WHERE
CatID = 1

Try it,

David
-Original Message-
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:39 PM
To: CF-Talk
Subject: RE: SQL custom Order by

-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by

I doubt this is possible, but is there a way to do a custom order by
based
 on the order I provide?I need the SQL results to be in a specific
order
 based on the catID, but not in ascending or descending order. The
order
 that I want them in is 10,3,5,1,7,4,9,2,6,8.

(Not sure this is possible in Sql Server, cause I don't use that).

I would write a custom function to do this.So you would do:

select catID, mysort(catID) srt
from foo
order by srt;

In the function, just return what order you want given the catId.
 [Todays Threads] 
 [This Message] 
 [Subscription] 
 [Fast Unsubscribe] 
 [User Settings]