Keep order of "field in (list)" 1

[jlsmithhartfiel]

Hi!

My query is:
select l.label
from listbox l
where l.fieldname = 'Municipality'
and l.value in ('rams','ak','cret')

I'm looking for the easiest way to display the records in the same order as 'rams','ak','cret'. Currently it sorts them in alphabetical order of l.label.

TIA!


Jessica

 

[vbkris]

and l.value in ('rams','ak','cret')

can u give me what this does (i have not used this sql command), maybe if i know what u want i can help u.

Known is handfull, Unknown is worldfull

 

[Digga]

vbkris,

this is the IN operator try here for info:

http://www.dmxzone.com/showDetail.asp?TypeId=2&NewsId=4257

Digga

Sharing Knowledge Saves Valuable Time!

 

[JamesLean]

If you are using SQL Server then this will work:

SELECT label
FRO listbox
WHERE fieldname = 'Municipality'
  AND value in ('rams','ak','cret')
ORDER BY CASE value
  WHEN 'rams' THEN 1
  WHEN 'ak' THEN 2
  WHEN 'cret' THEN 3
  END

--James

 

[jlsmithhartfiel]

Thanks for the example James! I think that will work for me. It's actually in a asp page, so I dynamically build the sql query (probably should have included that info ). The "in" list comes from another page, but I should be able to do this:

if sMunicipalities <> &quot;&quot; then
  dim tmpArray, iCtr
  tmpArray = Split(sMunicipalities, &quot;,&quot;)
  sql = &quot;select l.label &quot; _
      & &quot;from listbox l &quot; _
      & &quot;where l.fieldname = 'Municipality' &quot; _
      & &quot;and l.value in (&quot; & sMunicipalities & &quot;) &quot; _
      & &quot;order by case l.value &quot;
  for iCtr = 0 to ubound(tmpArray)
    sql = sql _
        & &quot; when &quot; & tmpArray(iCtr) & &quot; then &quot; & iCtr
  next
  sql = sql & &quot; end&quot;
  objData.Open sql
  Do While Not objData.EOF and Len(sMList) < 25
    if sMList <> &quot;&quot; then
      sMList = sMList + &quot;,&quot;
    end if
    sMList = sMList + objData(&quot;label&quot;)
    objData.MoveNext
  Loop
  objData.Close
  sMList = mid(sMList,1,25)
end if

Jessica