[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Order SQL Query Results in Same Order as Aggregate Parameter of "IN" Clause

Hi,

Would it be possible to make the result set of the following statement to be in the same order as the numbers in the "IN" clause.

SELECT ITEM_NO FROM ITEMS WHERE ITEM_NO IN ("00751", "00854", "00993", "00465", "00882")

Desired Result Set:

00751
00854
00993
00465
00882


Thanks in advance.

Ariel
0
AD1080
Asked:
AD1080
  • 4
  • 3
  • 2
  • +1
1 Solution
 
mcs0506Commented:
Hi,
The Answer is yes you can use IN clause but there is a little mistake in SQL, use single quote instead of double quotes if ITEM_NO is of character type.

SELECT ITEM_NO FROM ITEMS WHERE ITEM_NO IN ('00751', '00854', '00993', '00465', '00882')

If ITEM_NO is integer then add just integer value in IN clause
e.g.
SELECT ITEM_NO FROM ITEMS WHERE ITEM_NO IN (00751, 00854, 00993, 00465, 00882)

Regards,

Dani
0
 
AD1080Author Commented:
Hi,

Thanks for the reply, and forgive the type error with the double quotes.  

My question still remains unanswered though.  The default result set for this query will be ordered in numerical order.  I want to preserve the random order of the "IN" cause parameter.

Thanks in advance for your continued help with this question.

Ariel
0
 
mcs0506Commented:
Hi.
Do you fetch ITEM_NO from other table on certain criteria or you have you have a comma separated string of item numbers?

Dani
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
AD1080Author Commented:
Hi,

I have a comma separated string of items that I'm gathering from an Excel application.      

Thanks,

Ariel
0
 
LowfatspreadCommented:
only by generating a specify order by clause to natch it...

order by
case itemno when '00751' then 1 when  '00854' then 2 when  '00993' then 3 when  '00465' then 4 when  '00882' then 5
 else 99999999 end
0
 
LowfatspreadCommented:
of course if you have the inlist AS A COMMA DELIMITED STRING

THEN USE a function to convert it into a function derived table... and calculate the position of the term at that time
and reference you sort position that way....

there are plenty of references to the basic function to split a comma delimited string in EE or google...

let us know if you need additional assistance...
0
 
AD1080Author Commented:
Hi,  

This works great.

This is exactly what I was looking for.  Thanks a lot,

Ariel
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just for your information, I have been confronted with this question a couple of times, and I want to post my suggestion:
see this article, aka the function dbo.ParmsToList(): http://www.experts-exchange.com/A_1536.html
presuming the list of values from from a delimited list, you could write your query as:

select t.*
  from dbo.ParmsToList('00751,00854,00993,00465,00882') f
  join yourtable t
     on t.ITEM_NO  = f.value
 order by f.row_num

Open in new window


cheers
0
 
LowfatspreadCommented:
nice point on the function angeliii

i'd forgotten the specifics of your article...

the one caveat is of course that the original list must not contain duplicates or needs to be deduplicated otherwise additional rows will be returned...
via the join which isn't the case with the in method

select t.*
  from (select [value],min(row_num) as row_num
             from dbo.ParmsToList(@yourdelimitedlist)
              group by [value]
          ) as f
  join yourtable t
     on t.ITEM_NO  = f.value
 order by f.row_num




0
 
AD1080Author Commented:
Thanks Angellll and Lowfatspread,

This alternative solution appears to be actually what I needed.  

I haven't had a chance to test, but from what I read quickly, it appears this will allow rows in the record set where all the fields are null except that item in the parameter list.  

This is  exactly what  I was after.

Thanks again,

Ariel
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now