[thelist] SQL order by

Michael K. Ahn ahnfire at optonline.net
Fri Feb 7 09:17:02 CST 2003


Looks like the problem is, the search algorithm doesn't necessarily go
through your set in order (6,5,7,etc).  The SQL is concerned with matches in
to your set, rather than going through in any particular order, I'm
surprised it even comes out in numerical order.

If you want to do the ordering completely in the DB, I suggest that you
create another table with the key fields you want, and an ordering criteria.
Ie
tblEventPriority
EventCategory, EventOrder
6, 1
5, 2
7, 3
etc.

Join that to your query and you should be good (do "Order By
tblEventPriority.EventOrder").  Otherwise, you can always manipulate the
recordset in your code.  Hope that helps.

michael

-----Original Message-----
From: thelist-admin at lists.evolt.org [mailto:thelist-admin at lists.evolt.org]
On Behalf Of Drew Shiel
Sent: Friday, February 07, 2003 10:03 AM
To: thelist at lists.evolt.org
Subject: [thelist] SQL order by


[Using MS SQL Server, I think v7.0 or so]

I have SQL as follows:

SELECT EventCategory.EventCategoryID, EventCategory.EventCategory,
EventType.EventTypeID, EventType.EventType, [snip many more], FROM
EventCategory (nolock), EventType (nolock) where
(EventCategory.Eventcategoryid = EventType.EventCategoryID) AND
EventType.EventTypeHeadline <> '' AND EventCategory.EventCategoryID in
(6,5,7,8,10,11,12,13,14,16,17 ) order by EventCategory.EventCategory,
EventType.EventType

Now, the important bit for this question is:

EventCategory.EventCategoryID in (6,5,7,8,10,11,12,13,14,16,17 )

where the numbers are not in numeric order.

Even if I remove the "ORDER BY" part, the results come out in numeric
order. Is there any way to preserve the order I have set (6,5,7, etc)?

(The order isn't very different in this version, and it's not frightfully
urgent. In future versions, however, I may want vastly different ones, like
17,2,5,11,6... )

Cheers,
Drew.


Drew Shiel                               webmaster at swiftpay.com
                                                     +353-1-2365705
------------------------------------------------------------------
Swiftpay -- The best way to pay online -- http://www.swiftpay.com

--
* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt !





More information about the thelist mailing list