Hey Kevin, Ya I know thousands of items in the IN clause is not a good way, and it takes a while but from all the other process I've tried it has been actually the quickest (join for instance takes longer on my tests). Like yourself though I'm quiet clueless on what another way to do this might be even though I believe there should be one.
As it is right now I simply think I need a faster server that could handle such processes faster. It gets even worse when I try to do a SUM() when dealing with thousands of records.. Now that takes a long time. But other then creating cache type tables when that happens I don't know a work around for that either. Best, Yoed -----Original Message----- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Monday, August 04, 2003 10:59 AM To: 'Yoed' Subject: RE: Order by umm OR? Wow. Thousands of items in your IN clause? That sounds like a justification for finding "another way" to do this. I don't have much in mind, but maybe joining to another table containing those ID's would help you further. Might be a worthy experiment. As for the FIELD() function, yes. Sounds great. And more terse than my CASE statement. One thing to consider is that the CASE statement is ANSI compliant, and I use it on Oracle, Sql Server, MySQL, and a host of other DBMS's. FIELD and ELT (the corollary) are mysql specific. Good luck, Kevin > > -----Original Message----- > From: Kevin Fries [mailto:[EMAIL PROTECTED] > Sent: Friday, August 01, 2003 3:06 PM > To: 'Yoed'; [EMAIL PROTECTED] > Subject: RE: Order by umm OR? > > > Not surprising, the dbms has no built-in support for ordering > rows by the filters in the where clause. But with a bit of > programming you should be able to construct a useful ORDER BY > clause to do what you want... > > SELECT * FROM company WHERE id='3' OR id='1' OR id='7' > ORDER BY CASE id when '3' then 1 when '1' then 2 when '7' > then 3 else 4 end; > > So you'll have to construct the ORDER BY to relate each > successive OR item into a 'when ... then' subclause. It's not > pretty, but it works. > > Kevin > > PS: consider using WHERE id in ( '3', '1', '7') instead for brevity. > > > > > -----Original Message----- > > From: Yoed [mailto:[EMAIL PROTECTED] > > Sent: Friday, August 01, 2003 12:34 PM > > To: [EMAIL PROTECTED] > > Subject: Order by umm OR? > > > > > > Hi, > > > > Lets say I have a query similar to: > > > > SELECT * FROM company WHERE id='3' OR id='1' OR id='7' ; > > > > I want to be able to get the results to come out ordered by > the order > > the ids were searched (the sequence in the WHERE OR; 3 > first then 1, > > then 7...), so the results would be like: > > > > Row | id | name > > ------------------- > > 0 | 3 | Joe's Co > > 1 | 1 | Buymart > > 2 | 7 | The big evil > > > > As it is the results returns it like id 1, then 3, and 7. > > > > Thanks for your help guys, > > Best, > > Yoed > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
