Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help on SQL Query

 
Mani Ram
Ranch Hand
Posts: 1140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Gregg Bolinger
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm going to move this to the JDBC forum where all the SQL gurus hang out.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic