tricky SQL? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: tricky SQL?

  1. #11
    Join Date
    Feb 2001
    Posts
    295
    I'll suppose you are doing a join between a table having location (table LOC, column LOCATION) with another having person names (table PERSON, column NAME). You must have a column linking these tables (let's call DEPT_ID). Try the query:

    select A.LOCATION, B.NAME
    from LOC A, PERSON B
    where A.DEPT_ID=B.DEPT_ID
    and B.DEPT_ID in
    (select distinct DEPT_ID from LOC where rownum<=3)
    ;



    Adriano.

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Thank you for the suggestion I will have to have a look since actually i am joining 8 tables :D So I dont know if this would be feasible

  3. #13
    Join Date
    Feb 2001
    Posts
    295
    The only difference is that you'll have to use 7 joins conditions instead of one. Despite ORDER BY, GROUP BY etc, your query won't be degradated by joining tables.


    Adriano.

  4. #14
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well the query is based on about 10 search conditions that's why I am not sure this would be feasible... anyway will try this later

  5. #15
    Join Date
    Jun 2000
    Location
    Toronto, ON, Canada
    Posts
    50
    Your problem is that you want to select the first three DISTINCT values from a table, not the first three rows. In order to do that, the query would be:

    select colA from (select distinct colA from tableA) where rownum<=3

    So first you have a resultset with all distinct values ant only after you apply the rownum to this resultset.
    These stated, you can join this resultset with anything you want:

    select t1.colA, t2.colB from (select colA from (select distinct colA from tableA) where rownum<=3) t1, table2 t2 where t1.colA=t2.colC //as an example

    I tried this type of query in one of my databases and the result was correct. The query was something like:
    select t.vehicle_id_no, v.vehicle_id from (select vehicle_id from (select distinct vehicle_id from vehicle_history) where rownum<=3) v, vehicles t where t.vehicle_id=v.vehicle_id
    Hope it helps you,
    Mara



  6. #16
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well I guess corrrelated subquery would work, still have to try it was too busy today ....

  7. #17
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Actually, jmodic was on the right track, as the analytical functions are your best bet, but before I get to the solution...

    There were many excellent answers here. The problem you are going to have is implementing them, since your original statement sounds rather complicated. Given this complication, however, one must also consider performance. (Personally, I think one should *always* consider performance, but that's just me :) ). With that in mind, restricting the result set at the earliest possible time would be best. Therefore, the suggestion of finding the disting values first, and restricting that set might be best. However, this can get extremely complicated if you do not know your ultimate result set. I mean, just because I pick 3 states that I know are in table B does not mean that those 3 states will appear in the final result set. Therefore, you would have to apply all the restrictive criteria just to get the distinct set of usable states, then re-join and re-restrict everything again to get the full results. So, while the performance-dictator in me screams that we should do everything humanly possible to restrict the result set as early as possible, I must admit that this may not be a case where that can be done. Mind you, I don't have all the details, so it *may* be possible and *should* be explored.

    However, having said all that, what you appear to need is almost what jmodic gave you, just a different analytical function:

    SELECT
    ---COLA,
    ---COLB
    FROM
    (
    ---SELECT
    ------DENSE_RANK() OVER (ORDER BY T1.COLA)
    ---------AS MYRANK,
    ------T1.COLA,
    ------T2.COLB
    ---FROM
    ------T1,
    ------T2
    ---WHERE
    ------...
    ---)
    WHERE
    ---MYRANK <= 3


    Hope this helps,

    - Chris

    BTW - call me slow, but am I the last one to know that Oracle now supports the CASE construct?? I would have thought that one would have been on the front page of the POST! :) I am beside myself with joy - I'm never going to have to write a DECODE again!!

  8. #18
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Chris,

    You ar ecorrect again with your answer. When I suggeted the use of ROW_NUM() function I didn't correctly understand what pando wants - I thought he wants to select persons for every city, but at the same time to limit the number of persons per city to 3. Now you've comme with the right function for his actual bussines rule.

    As regarding the CASE operator (or is it a function?):
    I would say FINALY! AFAIK other databases supported CASE for quite a long time before Oracle included it in 8.1.6. I said FINALY, but at the same time something in me says: "Argh, I have developed quite a few tricks and techniques in using DECODE, now I will not be able to show around with those tricks anymore;) . Seriously, it is funny that some of techniques with DECODE were not published widely (or not published at all), for example how to avoid nesting decodes in complicated IF-ELSIF logic, how to implement range operators (GREATER THEN, ....) in decode and so on. I've had a presentation at our local usergroup conference devoted entirely to DECODE.

    But seriously, the introduction of CASE in oracle is true relief! When I had to read my own decode construct few weeks after I've wrote them, I had to study their logic again from the beginning. The logic of the complicated DECODEs is usually so hard to "decode", while now you look at CASE once and its logic is immediatelly selfunderstandable.

    BTW, the railroad diagram of CASE in 8.1.6 documentation has bug - there should be no comma (,) between subsequent WHEN comparisons.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #19
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    [QUOTE][i]Originally posted by chrisrlong [/i]
    [B]Actually, jmodic was on the right track, as the analytical functions are your best bet, but before I get to the solution...

    However, this can get extremely complicated if you do not know your ultimate result set. I mean, just because I pick 3 states that I know are in table B does not mean that those 3 states will appear in the final result set. Therefore, you would have to apply all the restrictive criteria just to get the distinct set of usable states, then re-join and re-restrict everything again to get the full results.
    [/B][/QUOTE]

    I did run into this problem of restricting twice with correlated query, I am trying with this analytic function will let you know the results
    And perfomance is very important in this query because itīs a search engine query so basically it will be called again and again with different crititerias

    Cheers

    [Edited by pando on 03-07-2001 at 03:50 AM]

  10. #20
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Back again but with problems, I am not sure if I partitioned the wrong columns.
    The query is

    SELECT * FROM
    (SELECT
    IC_PROMOCIONES.NOMBRE,
    IC_MODELOS.COD_RED,
    IC_MODELOS.COD_AGE,
    IC_MODELOS.COD_PRM,
    IC_MODELOS.COD_MOD,
    DENSE_RANK() OVER (PARTITION BY
    IC_PROMOCIONES.COD_RED,
    IC_PROMOCIONES.COD_AGE,
    IC_PROMOCIONES.COD_PRM
    ORDER BY
    IC_PROMOCIONES.COD_RED,
    IC_PROMOCIONES.COD_AGE,
    IC_PROMOCIONES.COD_PRM) MYRANK
    FROM
    IC_MODELOS,
    IC_PROMOCIONES,
    IC_EMPRESA,
    IC_POBLACION,
    IC_TIPINMUEB,
    IC_SBTINMUEB,
    IC_MONEDA
    WHERE
    IC_PROMOCIONES.COD_PAIS = 2 AND
    IC_PROMOCIONES.COD_COM = 15 AND
    IC_PROMOCIONES.COD_PRV = 28 AND
    IC_MODELOS.COD_TIP_INMU = 8 AND
    IC_MODELOS.COD_MON = 1 AND
    IC_PROMOCIONES.COD_RED = IC_MODELOS.COD_RED AND
    IC_PROMOCIONES.COD_AGE = IC_MODELOS.COD_AGE AND
    IC_PROMOCIONES.COD_PRM = IC_MODELOS.COD_PRM AND
    IC_MONEDA.COD_MON = IC_MODELOS.COD_MON AND
    IC_POBLACION.COD_POB = IC_PROMOCIONES.COD_POB AND
    IC_TIPINMUEB.COD_TIP_INM = IC_MODELOS.COD_TIP_INMU AND
    IC_SBTINMUEB.COD_SBT_INM = IC_MODELOS.COD_SBT_INMU AND
    IC_EMPRESA.COD_AGE = IC_PROMOCIONES.COD_AGE
    ORDER BY
    IC_POBLACION.DES_POB,
    IC_EMPRESA.RZN_SCL,
    IC_PROMOCIONES.NOMBRE,
    IC_MODELOS.PRECIO_PUBLICACION
    )
    WHERE MYRANK <= 3


    The partition:
    PARTITION BY
    IC_PROMOCIONES.COD_RED,
    IC_PROMOCIONES.COD_AGE,
    IC_PROMOCIONES.COD_PRM

    These 3 columns forms the primary key and are the one that I want to obtain the distinct values. The results I got are dodgy:

    ----COD_RED-----COD_AGE-----COD_PRM-----MYRANK

    2----------596-------3331----------1
    2----------858-------2--------------1
    2----------858-------2--------------1
    2----------849-------1--------------1
    2----------849-------1--------------1
    2----------600-------2345----------1
    2----------600-------2345----------1
    2----------1363------7777----------1

    Instead of (I think)

    MY RANK

    8
    7
    6
    5
    4
    3
    2
    1

    Well actually it should be 1,2,3,4,5,6,7,8 ;)

    [Edited by pando on 03-07-2001 at 03:54 AM]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width