Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Need help Regarding executing select query

Reply
Thread Tools

Need help Regarding executing select query

 
 
Pradeep
Guest
Posts: n/a
 
      11-09-2007
I have one problem in getting Results using select Query...

Query:
------------ -
Select product_sys_ id,product_ name from Product where product_sys_
id in(9,6,4,1,2) ;

ResultActual)
------------ --------- --------- ----
product_sys_ id product_name
------------ --------- --------- --------- -----
1 PR1
2 PR9
4 PR5
6 PR787
9 PR657

Observe that in the Result product_sys_ ids are getting in the sorted
order not in the order specified in the Select Query...

I want to get the Result of product_sys_ ids in the order specified
in the select
Query....i.e. ,

ResultExpected)
------------ --------- ------
product_sys_ id product_name
------------ --------- --------- --------- -----
9 PR657
6 PR787
4 PR5
1 PR1
2 PR9

Can anybody know the solution ???

 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      11-09-2007
Pradeep wrote:
> I have one problem in getting Results using select Query...
>
> Query:
> ------------ -
> Select product_sys_ id,product_ name from Product where product_sys_
> id in(9,6,4,1,2) ;
>
> ResultActual)
> ------------ --------- --------- ----
> product_sys_ id product_name
> ------------ --------- --------- --------- -----
> 1 PR1
> 2 PR9
> 4 PR5
> 6 PR787
> 9 PR657
>
> Observe that in the Result product_sys_ ids are getting in the sorted
> order not in the order specified in the Select Query...
>
> I want to get the Result of product_sys_ ids in the order specified
> in the select
> Query....i.e. ,
>
> ResultExpected)
> ------------ --------- ------
> product_sys_ id product_name
> ------------ --------- --------- --------- -----
> 9 PR657
> 6 PR787
> 4 PR5
> 1 PR1
> 2 PR9
>
> Can anybody know the solution ???


Use an ORDER BY clause in your SELECT.

--
Lew
 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      11-09-2007
Lew wrote:
> Pradeep wrote:
>> I have one problem in getting Results using select Query...
>>
>> Query:
>> ------------ -
>> Select product_sys_ id,product_ name from Product where product_sys_
>> id in(9,6,4,1,2) ;
>>
>> ResultActual)
>> ------------ --------- --------- ----
>> product_sys_ id product_name
>> ------------ --------- --------- --------- -----
>> 1 PR1
>> 2 PR9
>> 4 PR5
>> 6 PR787
>> 9 PR657
>>
>> Observe that in the Result product_sys_ ids are getting in the sorted
>> order not in the order specified in the Select Query...
>>
>> I want to get the Result of product_sys_ ids in the order specified
>> in the select
>> Query....i.e. ,
>>
>> ResultExpected)
>> ------------ --------- ------
>> product_sys_ id product_name
>> ------------ --------- --------- --------- -----
>> 9 PR657
>> 6 PR787
>> 4 PR5
>> 1 PR1
>> 2 PR9
>>
>> Can anybody know the solution ???

>
> Use an ORDER BY clause in your SELECT.


To expand on that - SELECT is not guaranteed to return any particular order
absent an ORDER BY clause.

You don't say how you get the more "random"-seeming results. Was it via a
command line SQL tool, such as psql?

That tool in turn communicates with the RDBMS engine, as the JDBC driver must.
That tool, like your JDBC calls, is a black box to the programmer / user.
We don't get to know, much less influence, how the RDBMS receives the SELECT
or how it plans it, save that it must conform to SQL semantics. SQL semantics
explicitly disclaim order for the results.

Ergo, if you do not ORDER BY your query, you have to take it in any order that
it has. Any system or technique that guarantees the order of the SELECT any
other way will be non-compliant.

That said, we do not know that the RDBMS-to-JDBC link is re-ordering your
results. Perhaps the result set is arriving at the JVM in one order, and at
your ResultSet (or RowSet) in a different order. If so, there are two
possible reasons:

1) The JDBC mechanism likes to arbitrarily re-order results. This is
extremely unlikely, since in the general case there is no need to do this and
it would slow performance. Java authors and API writers are already sensitive
to accusations that Java is slow; they're unlikely to bog JDBC down without
any benefit.

2) Your application is ordering the result set. This could be true, and if so
that is good news, because it means that you can fix it.

Can you tell whether the RDBMS is ordering the results in this unexpected way,
or if it's happening in your program or the JDBC layer?

--
Lew
 
Reply With Quote
 
Ed Webb
Guest
Posts: n/a
 
      11-09-2007
Lew wrote:
> Pradeep wrote:
>> I have one problem in getting Results using select Query...
>>
>> Query:
>> ------------ -
>> Select product_sys_ id,product_ name from Product where product_sys_
>> id in(9,6,4,1,2) ;
>>
>> ResultActual)
>> ------------ --------- --------- ----
>> product_sys_ id product_name
>> ------------ --------- --------- --------- -----
>> 1 PR1
>> 2 PR9
>> 4 PR5
>> 6 PR787
>> 9 PR657
>>
>> Observe that in the Result product_sys_ ids are getting in the sorted
>> order not in the order specified in the Select Query...
>>
>> I want to get the Result of product_sys_ ids in the order specified
>> in the select
>> Query....i.e. ,
>>
>> ResultExpected)
>> ------------ --------- ------
>> product_sys_ id product_name
>> ------------ --------- --------- --------- -----
>> 9 PR657
>> 6 PR787
>> 4 PR5
>> 1 PR1
>> 2 PR9
>>
>> Can anybody know the solution ???

>
> Use an ORDER BY clause in your SELECT.
>


That won't help as you can only order numerically ascending or
descending not in the random order pradeep requires. I know of no way to
use SQL to arbitrarily order the resultset. You will need to take the
data returned from the database and order it yourself.

Ed!
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      11-09-2007
Ed Webb wrote:
> Lew wrote:
>> Pradeep wrote:
>>> I have one problem in getting Results using select Query...
>>>
>>> Query:
>>> ------------ -
>>> Select product_sys_ id,product_ name from Product where product_sys_
>>> id in(9,6,4,1,2) ;
>>>
>>> ResultActual)
>>> ------------ --------- --------- ----
>>> product_sys_ id product_name
>>> ------------ --------- --------- --------- -----
>>> 1 PR1
>>> 2 PR9
>>> 4 PR5
>>> 6 PR787
>>> 9 PR657
>>>
>>> Observe that in the Result product_sys_ ids are getting in the sorted
>>> order not in the order specified in the Select Query...
>>>
>>> I want to get the Result of product_sys_ ids in the order specified
>>> in the select
>>> Query....i.e. ,
>>>
>>> ResultExpected)
>>> ------------ --------- ------
>>> product_sys_ id product_name
>>> ------------ --------- --------- --------- -----
>>> 9 PR657
>>> 6 PR787
>>> 4 PR5
>>> 1 PR1
>>> 2 PR9
>>>
>>> Can anybody know the solution ???

>>
>> Use an ORDER BY clause in your SELECT.
>>

>
> That won't help as you can only order numerically ascending or
> descending not in the random order pradeep requires. I know of no way to
> use SQL to arbitrarily order the resultset. You will need to take the
> data returned from the database and order it yourself.


You don't know that for sure, because you don't know what other columns
(perhaps even OID) might suit. You are, of course, absolutely correct if no
such column exists. What we do know is that there is no way to guarantee a
SELECT's order without an ORDER BY.

Besides, the question isn't how to achieve a particular order, but how to
match the order emitted by the RDBMS. The OP isn't asking how to impose the
random-seeming order, AFAICT, but suspects that the Java system is somehow
altering the order as returned by the RDBMS. I doubt that this is the case.
I suspect that some part of the application is ordering the data. I've been
wrong about that sort of thing before, of course. There isn't enough
information on the board yet to do more than speculate.

--
Lew
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      11-09-2007
Lew wrote:
> Ed Webb wrote:
>> Lew wrote:
>>> Pradeep wrote:
>>>> I have one problem in getting Results using select Query...
>>>>
>>>> Query:
>>>> ------------ -
>>>> Select product_sys_ id,product_ name from Product where product_sys_
>>>> id in(9,6,4,1,2) ;


> Besides, the question isn't how to achieve a particular order, but how
> to match the order emitted by the RDBMS. The OP isn't asking how to
> impose the random-seeming order, AFAICT, but suspects that the Java
> system is somehow altering the order as returned by the RDBMS. I doubt
> that this is the case. I suspect that some part of the application is
> ordering the data. I've been wrong about that sort of thing before, of
> course. There isn't enough information on the board yet to do more than
> speculate.


Oy, now I see it. They want to order the result by the order of the items in
the IN clause. Oy, gevalt.

Not without a function to re-order based on the IN clause order, and that's a
hack. Really, the best advice is: Don't.

It's hard for me to imagine a business case for coercing a SELECT ... WHERE
.... IN clause to match the output order. Ordering is for result sets, not
query clauses.

Note that the SQL statement has the exact same meaning no matter what order
the IN set has. It would be a violation of SQL semantics for that to make a
difference.

So, OP, figure out an order that makes sense for your result set. Make sure
there's a column or function that you can include in the column set and use
for ORDER BY. Under no circumstances craft a WHERE ... IN set wherein you
think order of the set matters, because it doesn't.

--
Lew
 
Reply With Quote
 
Are Nybakk
Guest
Posts: n/a
 
      11-09-2007
Pradeep wrote:
> I have one problem in getting Results using select Query...

*snip*
>


And how is this java-related? Even comp.lang.java.databases would be a
better place for such a question.
 
Reply With Quote
 
Wojtek
Guest
Posts: n/a
 
      11-09-2007
Pradeep wrote :
> I have one problem in getting Results using select Query...
>
> Query:
> ------------ -
> Select product_sys_ id,product_ name from Product where product_sys_
> id in(9,6,4,1,2) ;
>
> ResultActual)
> ------------ --------- --------- ----
> product_sys_ id product_name
> ------------ --------- --------- --------- -----
> 1 PR1
> 2 PR9
> 4 PR5
> 6 PR787
> 9 PR657
>
> Observe that in the Result product_sys_ ids are getting in the sorted
> order not in the order specified in the Select Query...
>
> I want to get the Result of product_sys_ ids in the order specified
> in the select
> Query....i.e. ,
>
> ResultExpected)
> ------------ --------- ------
> product_sys_ id product_name
> ------------ --------- --------- --------- -----
> 9 PR657
> 6 PR787
> 4 PR5
> 1 PR1
> 2 PR9
>
> Can anybody know the solution ???


Which DB engine are you using? I am using the same pattern with MS SQL
Server and I get the results in the expected order.

--
Wojtek


 
Reply With Quote
 
Wojtek
Guest
Posts: n/a
 
      11-09-2007
Lew wrote :
> It's hard for me to imagine a business case for coercing a SELECT ... WHERE
> ... IN clause to match the output order.


To reduce the impact of complex where clauses where you are paginating
the results.

Consider an application which may retrieve 1000+ rows. You do not want
to feed all 1K rows back to a Web app, so you want to be able to show
the first 20, then the next 20, and so on. Maybe let the user select
the range from a drop list (1 - 20 of 1000., 21 - 40 of 1000, ...).

So you run the query with the complex where clause retrieving ONLY the
primary ID column. Store this in an array. It is now trivial to select
a sub-set of the result from the array, use it in a "where in" clause
and get the sub-set rows in the correct order.

--
Wojtek


 
Reply With Quote
 
Wojtek
Guest
Posts: n/a
 
      11-09-2007
Wojtek wrote :
> Pradeep wrote :
>> Can anybody know the solution ???

>
> Which DB engine are you using? I am using the same pattern with MS SQL Server
> and I get the results in the expected order.


Hmmm, re-reading the MSSQL documentaton for "where in" does not mention
anything about retrieval order. So this must be a side effect which
just happens to work.

Sigh, now I need to re-design my implementation

--
Wojtek


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need some help with Ruby query building and query time Alpha Blue Ruby 3 07-28-2009 06:52 PM
? regarding Executing/Spawning Ruby calls in parallel Venks Ruby 0 01-18-2008 02:28 AM
Trying to query the Address table data of AdventureWorks database from Query Analyzer - need help! Learner ASP .Net 1 01-30-2006 08:58 PM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM
Server Error in '/' Application - when executing stored query - novice question sean ASP .Net 0 12-02-2003 05:43 AM



Advertisments