• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Help on SQL Query

 
Ranch Hand
Posts: 1140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm going to move this to the JDBC forum where all the SQL gurus hang out.
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
It will give me the powers of the gods. Not bad for a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic