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.
| Author |
Topic |
|
div66
Starting Member
3 Posts |
 Posted - 2005-03-19 : 14:58:04
|
| I am just in the process of updating my .asp site from Access to MSSQL, I used to be able to select records by id, following the order I selected as below. SELECT * FROM directory WHERE id IN ('13789','13870','13949') ORDER BY id='13789',id='13870',id='13949'MSSQL throws up the following error Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='. What I am trying to do is select a number of records based on their id’s in the order of those id’s. I don't want then assending or desending numerical order, they must be in the order I select.I am an SQL and Access novice, but the above did work for me, and I want to replicate it with MSSQL.Many Thanks for you Assistance. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-19 : 15:06:59
|
| [code]ORDER BY case id when '13789' then 1 when '13870' then 2 when '13949' then 3 else 99999 end[/code]CODO ERGO SUM |
 |
|
|
div66
Starting Member
3 Posts |
Posted - 2005-03-19 : 15:24:59
|
Many Thanks that seems to work  Is there a better solution?The problem with creating a new table would be I need to do this around 10,000 times a day, would this cause problems.As I say I am total novice.Thanks in advance for your answers. |
|
|
|
div66
Starting Member
3 Posts |
Posted - 2005-03-19 : 15:27:27
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6055Perhaps I should have looked at this first but how would I adapt this to my example above?SQL is double dutch to me, well sort of, as I say I am a novice.ThanksDiV |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-19 : 16:51:46
|
To answer your first question: I'm sure there is better solution, but since you didn't supply any more information or criteria, this one seems to work.I not sure what you mean about creating a new table. I only supplied an ORDER BY clause to use in a SELECT. Are you referencing the link you supplied in the later post?quote:
Originally posted by div66...Is there a better solution?The problem with creating a new table would be I need to do this around 10,000 times a day, would this cause problems...
CODO ERGO SUM |
|
|
|
|
|
|
|
|
selecting ids by id order