aspose file tools*
The moose likes JDBC and the fly likes Help on SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help on SQL Query" Watch "Help on SQL Query" New topic
Author

Help on SQL Query

Mani Ram
Ranch Hand

Joined: Mar 11, 2002
Posts: 1140
Assume that there is a table TABLE1 containing the following data



There is another table TABLE2 containing the MEMBER_NO in it.

Now I want a query to get the records from TABLE1 such that,
The records from TABLE1 should be sorted based on the NUM_OF_POSTS and
If the MEMBER_NO exists in TABLE2, it should come at top of the list
If more than one MEMBER_NO exists in TABLE2, it should be sorted based on NUM_OF_POSTS

For example, if the TABLE2 contains the 290 (Jim) and 16595 (Ilja) in it, the result should be as follows


Can someone help me?
[ August 12, 2004: Message edited by: Mani Ram ]

Mani
Quaerendo Invenietis
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I'm going to move this to the JDBC forum where all the SQL gurus hang out.


GenRocket - Experts at Building Test Data
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Mani,

OK, this may not work straight out of the box as you didn't mention which RDBMS you're using and it does make a bit of a difference here. From your spec, I've called TABLE 1 "member" and TABLE 2 "privileged_member".


I've tried to make it as standard as possible but you may need to add DESC to "ORDER BY pm.member_no" depending on whether your DB sorts NULL high or low. If that doesn't work you'll need to use the IsNull or IfNull function (depending on DB) to convert NULL to, say -1, so that privileged members always appear at the top.

Your RDBMS may also struggle with the ANSI outer join syntax, especially if it's something pants like MySQL.

If you still have trouble then re-post and I'll see if I can help out.

Fortunately no guru required this time.

Jules
Mani Ram
Ranch Hand

Joined: Mar 11, 2002
Posts: 1140
Originally posted by Gregg Bolinger:
I'm going to move this to the JDBC forum where all the SQL gurus hang out.


Where ever you wish
My initial idea was to post it in the JDBC forum (because I can expect more replies in JDBC forum than in GC forum), but then I thought that it will be moved to General Computing forum with a comment saying "This has nothing to do with Java. So I'm moving it to....."
Mani Ram
Ranch Hand

Joined: Mar 11, 2002
Posts: 1140
Thanks a lot Jules. I use Oracle and the above query gives me the expected result.
But, the query takes noticeably long time even for a very small record count (around 50 in the "member" table and around 20 in the "privileged_member" table).
Is there a way to optimize this? I would like a database independent query, but if there is a way to improve the perfomance using database specific functions, I'm ready for the trade-off
Mani Ram
Ranch Hand

Joined: Mar 11, 2002
Posts: 1140
Sorry. It didn't work.
That query moved all the member_no from privileged_member to the top, but it didn't sort those moved-up records according to the number of posts (it ordered them by the member_no instead).
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hmmm, that'll be because the columns in the ORDER BY clause are the wrong way round. D'oh! :roll:

I can't see why it would take a long time. Do you have unique indexes on member_no? If it's still seems poor then post the query plan and I'll see if I can see why. Are you running it from Java or directly through SQL*Plus? If the former there could be other reasons for slow performance.

You could also try it with Oracle's own OJ syntax:

Jules
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Mani,
Definitely check for the indexes. Also, try running explain (it's in the Oracle help if you search for explain) to see the execution path of the query. If you post that here, we might be able to see what the problem is.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Help on SQL Query