Need help Regarding executing select query

[Pradeep]

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 ???

 

[Lew]

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]

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?

 

[Ed Webb]

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!

 

[Lew]

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]

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.

 

[Are Nybakk]

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.

 

[Wojtek]

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]

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]

Wojtek wrote :

Pradeep wrote :

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 :-(

 

[Lew]

Lew wrote :

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.

Again, SQL does NOT make guarantees about the order of returned results absent
an ORDER BY clause, so in general the described technique will not work.

Furthermore, the semantics of SQL require that the same results be returned
(except for order) regardless of the order of items in the IN set. Taken
together, you cannot have SQL guarantee that the order of a result set matches
the order of items in the IN clause unless you hack the IN clause to match the
ORDER BY clause. In that case it is still the ORDER BY clause that determines
the order of returned results.

Let me state this again: SQL semantics forbid the IN clause from having any
guaranteed effect on the order of returned results.

 

[Lew]

Wojtek wrote :

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 :-(

Yes, because SQL semantics require that IN not have any reliable influence on
result set order. You could easily get the exact same results irrespective of
the order of items in the IN set.

There is no semantic relationship between the IN clause and the result order
in SQL. None. Zip. Nada. Nil.

 

[Patricia Shanahan]

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

Could you construct an Id_order table like this?

primary_key product_sys_id
1 9
2 6
3 4
4 1
5 2

You could then do a join between Id_order and Product, ordering the
result by Id_order.primary_key

Patricia

 

[Chris ( Val )]

Yes, because SQL semantics require that IN not have any reliable influence on
result set order. You could easily get the exact same results irrespective of
the order of items in the IN set.

There is no semantic relationship between the IN clause and the result order
in SQL. None. Zip. Nada. Nil.

Yes, that is true, but the result set can be ordered quite easily:

<TESTED>

Select product_sys_id, product_name FROM Product
WHERE product_sys_id IN( 9, 6, 4, 1, 2 )
ORDER BY
CASE WHEN product_sys_id = 9 THEN 1
WHEN product_sys_id = 6 THEN 2
WHEN product_sys_id = 4 THEN 3
WHEN product_sys_id = 1 THEN 4
WHEN product_sys_id = 2 THEN 5
ELSE NULL
END;

</TESTED>

--
Chris

PS: I sent a reply about using "CASE WHEN" before
I went to bed last night, but it looks like it
didn't make it here (sent via google).

 

[Lew]

... the result set can be ordered quite easily:

<TESTED>

Select product_sys_id, product_name FROM Product
WHERE product_sys_id IN( 9, 6, 4, 1, 2 )
ORDER BY
CASE WHEN product_sys_id = 9 THEN 1
WHEN product_sys_id = 6 THEN 2
WHEN product_sys_id = 4 THEN 3
WHEN product_sys_id = 1 THEN 4
WHEN product_sys_id = 2 THEN 5
ELSE NULL
END;

</TESTED>

Excellent suggestion. Note that it should work equally well as
(not tested here)

Select product_sys_id, product_name FROM Product
WHERE product_sys_id IN( 1, 2, 4, 6, 9 )
ORDER BY
CASE WHEN product_sys_id = 9 THEN 1
WHEN product_sys_id = 6 THEN 2
WHEN product_sys_id = 4 THEN 3
WHEN product_sys_id = 1 THEN 4
WHEN product_sys_id = 2 THEN 5
ELSE NULL
END;

 

[Chris ( Val )]

Chris ( Val ) wrote:







Excellent suggestion. Note that it should work equally well as
(not tested here)

Select product_sys_id, product_name FROM Product
WHERE product_sys_id IN( 1, 2, 4, 6, 9 )
ORDER BY
CASE WHEN product_sys_id = 9 THEN 1
WHEN product_sys_id = 6 THEN 2
WHEN product_sys_id = 4 THEN 3
WHEN product_sys_id = 1 THEN 4
WHEN product_sys_id = 2 THEN 5
ELSE NULL
END;

Yes.

The IN clause is only responsible for returning
the specified result set, and the ORDER BY clause
in conjunction with the CASE WHEN clause are both
responsible for the ordering of the result set.

Note that the numbers following the THEN keyword
need not have started from 1, As long as they are
in ascending order, any start number would be fine.

 

[Adam Maass]

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.

If you were writing the database engine, how would you go about satisfying
the query, especially if the table is indexed on product_sys_id? You'd
probably sort your in-list, and then walk the index.

I'm willing to bet that the database is returning the rows in the order they
occur in the index. This is, of course, perfectly legal as the SQL spec does
not guarantee any particular order on a SELECT absent an ORDER BY clause.

-- Adam Maass

 

[Wojtek]

Lew wrote :

Yes, because SQL semantics require that IN not have any reliable influence on
result set order. You could easily get the exact same results irrespective
of the order of items in the IN set.

There is no semantic relationship between the IN clause and the result order
in SQL. None. Zip. Nada. Nil.

Why yes Lew, that is what I said...

 

[Lew]

Why yes Lew, that is what I said...

Please forgive me. Years of working for the Department of Redundancy
Department have left their mark.

 

[Daniel Pitts]

Please forgive me. Years of working for the Department of Redundancy
Department have left their mark.

Not to mention working for the Department of Redundancy Department has
left its mark.