[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

DB2 v6.1SQL - I need to return the same order of the values in the WHERE IN ('','') list

I have a list of INVOICE NO.s (IVNOs) that come from a JSP web page. The order in which they come from the page is important to keep to output back to the user. I have rights to write Stored procedures if need. The list of IVNOs varies, so my aim was to have JAVA assemble the list into the SQL in the WHERE clause. For example, WHERE IVNO in ('1200030', '12004000', '12000111', ect, etc). How do I return the IVNO's back in the same order?

Thanks!
0
leason2
Asked:
leason2
  • 5
  • 4
  • 2
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
As far I know, Values in WHERE condition can't influence the order of display in the result. If you really want to achieve this, you need to rely on some other logic.

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
FYI - Check this query, now the values are sorted by the same order as in WHERE condition because of sort by a Serial no generated in that order.
select *, case id when 102 then 1 when 100 then 2 when 103 then 3 end serial
from #table where id in (102, 100, 103)
order by serial

Open in new window

0
 
leason2Author Commented:
That was just my initial SQL try, I'm open to other ideas. Even a Stored Procedure.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Rajkumar GsSoftware EngineerCommented:
This is another logic.
declare @table table
(
	id	int identity(1,1),
	invoice_no	int
)
-- if order you want to sort is 9, 5, 1, 7 
-- insert in the order that you want to see
insert into @table values(9)
insert into @table values(5)
insert into @table values(1)
insert into @table values(7)

select invoice_no from @table 
order by id

/* -- result 
9
5
1
7
*/

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:

RajkumarGS nailed it. See example below,


select someStuff,
       INVO
  from deleteme
 where invo in ('12000030',
                '12004000',
                '12000111')
 order by case invo
            when '12000030' then 1
            when '12004000' then 2
            when '12000111' then 3
          end

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
@daveslash, I believe your query is also having the same logic as my previous query  ?
select *, case id when 102 then 1 when 100 then 2 when 103 then 3 end serial
from #table where id in (102, 100, 103)
order by serial

Open in new window

0
 
leason2Author Commented:
These are great ideas. Let me try these real quick. You guys are on the same logic , well done. I'll reply shortly!
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

"Great minds think alike."
0
 
Rajkumar GsSoftware EngineerCommented:
:)
0
 
leason2Author Commented:
These guys hit it dead on. I split the credit because I will use both solutions .
0
 
leason2Author Commented:
Thanks Gents! I awarded the points to both of you for the great code.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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