 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / November 2007Need help Regarding executing select query
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) ; Result:(Actual) ------------ --------- --------- ---- 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. , Result:(Expected) ------------ --------- ------ product_sys_ id product_name ------------ --------- --------- --------- ----- 9 PR657 6 PR787 4 PR5 1 PR1 2 PR9 Can anybody know the solution ??? > I have one problem in getting Results using select Query... > [quoted text clipped - 31 lines] > > Can anybody know the solution ??? Use an ORDER BY clause in your SELECT.  SignatureLew >> I have one problem in getting Results using select Query... >> [quoted text clipped - 33 lines] > > 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? SignatureLew >> I have one problem in getting Results using select Query... >> [quoted text clipped - 33 lines] > > 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! >>> I have one problem in getting Results using select Query... >>> [quoted text clipped - 38 lines] > 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. SignatureLew >>>> 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 [quoted text clipped - 4 lines] > 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. SignatureLew 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. SignatureWojtek :-) > Lew wrote : >> It's hard for me to imagine a business case for coercing a SELECT ... [quoted text clipped - 12 lines] > 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. SignatureLew >>>> I have one problem in getting Results using select Query... >>>> [quoted text clipped - 51 lines] > 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 > >> I have one problem in getting Results using select Query... > [quoted text clipped - 38 lines] > use SQL to arbitrarily order the resultset. You will need to take the > data returned from the database and order it yourself. Time for me to go to bed, but I'm pretty sure you can retain the order you want by using a "CASE WHEN" statement. -- Chris > > >> I have one problem in getting Results usingselectQuery... > [quoted text clipped - 42 lines] > can retain the order you want by using a "CASE WHEN" > statement. This was the post I mentioned elsethread. Good job google groups... only 4 days late! -- Chris > 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. Pradeep wrote : > I have one problem in getting Results using select Query... > [quoted text clipped - 31 lines] > > 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. SignatureWojtek :-) 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 :-( SignatureWojtek :-) > Wojtek wrote : >> Pradeep wrote : [quoted text clipped - 8 lines] > > 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. SignatureLew > > Wojtek wrote : > >> Pradeep wrote : [quoted text clipped - 15 lines] > 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). > ... the result set can be ordered quite easily: > [quoted text clipped - 12 lines] > > </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; SignatureLew > > ... the result set can be ordered quite easily: > [quoted text clipped - 26 lines] > 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. -- Chris Lew wrote : >> Wojtek wrote : >>> Pradeep wrote : [quoted text clipped - 15 lines] > 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... SignatureWojtek :-) > Why yes Lew, that is what I said... Please forgive me. Years of working for the Department of Redundancy Department have left their mark. SignatureLew >> Why yes Lew, that is what I said... > > 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. SignatureDaniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/> Lew wrote : >> Why yes Lew, that is what I said... > > Please forgive me. Years of working for the Department of Redundancy > Department have left their mark. That's ok, I too have had to work with marketing types. SignatureWojtek :-) > I have one problem in getting Results using select Query... > [quoted text clipped - 12 lines] > 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 Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.