tricky SQL? - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: tricky SQL?

  1. #21
    Join Date
    Jan 2001
    Posts
    23

    Probable soln...


    Hi Pando,
    I'm not very sure about the performance, but believe this is a

    solution.

    SELECT A.NAME, B.CITY, B.RNO FROM TABLE1 A, (SELECT CITY, ROWNUM RNO

    FROM (SELECT DISTINCT CITY FROM TABLE2) B
    WHERE A.XXX = B.XXX AND B.RNO <= 3

    This will give you the serial as you wish in the output as well will
    restrict the output to the value supplied to the RNO column.

    - Nanda.

  2. #22
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well it probably works but I will have to check for search criteria twice which would be a huge perfomance impact
    It would be my last choice if I cant get it working :0

  3. #23
    Join Date
    Feb 2001
    Posts
    9

    Try This

    I have taken two tables for this ,
    i hope this will solve ur req...

    other wise pl reply me

    table citymaster ----- table personmaster
    cno cno
    c_name personname

    sql:
    select cityname,p_name as personname from personmaster A,
    (select distinct cityno as cno, c_name as cityname from citymaster where rownum<4) master where
    A.cno=master.cno

    ........
    reply me
    komethagan.s

  4. #24
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Pando, please look at my solution again. The way you want to use the DENSE_RANK function is how I showed you - with a simple ORDER BY, not a PARTITION BY. You only want one set of rankings, not multiple. Further, exactly which field(s) do you want to break on? Your first example had 2 fields and you wanted to break on the first with the second as simple detail. Your current SQL has 3 fields. Do you want to break on the combination of all of them with the rest as detail, or do you want to break on the first 1 or 2 fields with the remaining 2 or 1 fields as detail?

    Let's assume you want all 3 columns. The next issue is that of ordering. The only way to get this counting is with the OVER(ORDER BY ...). Now, this will order the result set and then start counting distinct (multi-column) values. HOWEVER, this ORDER BY is completely different than the query's ORDER BY, which may lead to unexpected results.

    Going back to you original example. if the query returned:

    Column A---Column B
    New York----David
    New York----John
    New York----Alex
    Florida-------Phil
    L.A-----------David
    L.A-----------Jenny
    L.A-----------Boris
    L.A-----------Joseph
    Miami--------Alex
    Miami--------Will
    Atlanta-------Chris

    Then using the DENSE_RANK() OVER (ORDER BY ColumnA) would *internally* do this:
    Column A---MYRANK
    Atlanta-------1
    Florida-------2
    L.A-----------3
    L.A-----------3
    L.A-----------3
    L.A-----------3
    Miami--------4
    Miami--------4
    New York----5
    New York----5
    New York----5

    Which we are then restricting to MYRANK <=3. Note that the query's ORDER BY is on some other field (matching the original results up top). Therefore, the actual result set we would get back is:

    Column A---Column B
    Florida-------Phil
    L.A-----------David
    L.A-----------Jenny
    L.A-----------Boris
    L.A-----------Joseph
    Atlanta-------Chris

    However, note that paging down is easy and will get you the next set of results:

    WHERE MYRANK >3 and MYRANK <=6:

    Column A---Column B
    New York----David
    New York----John
    New York----Alex
    Miami--------Alex
    Miami--------Will


    As for nansbrain and komes - I hate to be a stickler on this point, but you really have to take the effort to read the existing 2 pages of replies before adding to the heap - both of those solutions were already suggested. Mind you, the other solutions might not have had the *exact* same code, but the idea of a distinct list restricted by rownum as a general solution was already discussed at length. If you think your post is truly a unique take on the situation, please take the time to say why.

    Lecture over :).

    Hope this helps,

    - Chris

  5. #25
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I have tried the distinct this morning but I was getting dodgy results

    when I issued

    SELECT
    DISTINCT
    IC_MODELOS.COD_RED,
    IC_MODELOS.COD_AGE,
    IC_MODELOS.COD_PRM
    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
    /

    I got this back

    COD_RED COD_AGE COD_PRM
    ---------- ---------- ----------
    2 325 1
    2 596 2223
    2 596 3331
    2 600 57
    2 600 1236
    2 600 2345

    So then in the same query I adde AND ROWNUM <= 3

    I only got this back

    COD_RED COD_AGE COD_PRM
    ---------- ---------- ----------
    2 596 2223

    I am a bit puzzled, any reason about this...?

  6. #26
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi Chris

    By the way I think I got it working, I will have to have a litte check on the result set but I think itīs correct. It worked after removing the partition part I was a bit confused about that part but itīs now clear.

    I am doing the inner view way to compare the perfomance.


    Thanks

  7. #27
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    That's because DISTINCT is pretty much the same as a GROUP BY - it is done *after* the resultset is built. ROWNUM is determined *as* the resultset is built. Therefore, you had


    A B C RowNUM
    1 1 1 1
    1 1 1 2
    1 1 1 3
    1 2 1 4
    ...

    Then, your ROWNUM <=3 gave you

    A B C RowNUM
    1 1 1 1
    1 1 1 2
    1 1 1 3


    ...and the distinct came back with

    A B C
    1 1 1

    ...tada :)

    So have you tried the corrected version of the DENSE_RANK function yet?

    - Chris

  8. #28
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Oops, looks like we overlapped a little there - glad to see you got it worked out from the one side. Let us know if you run into trouble implementing the other side. And once you get it working, definitely let us know about the performance results!!

    Thanks,

    - Chris

  9. #29
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well the other side, the problem is about this DISCTINCT problem which is only returning me 1 row instead of 3
    So I am getting wrong result sets
    Adding a rownum column solved this

    [Edited by pando on 03-07-2001 at 12:33 PM]

  10. #30
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    That's because you are trying to do the distinct on the outside. You need to do it on the inside.

    going back to your original example (becaUSE IT'S IN A LANGUAGE i UNDERSTAND :) ) The trick is that you want a set of 3 States *that satisfy the query*. This entails 2 things

    - You want to limit the initial query to 3 states
    This could be accomplished throught the simplified FROM (SELECT * FROM (SELECT DISTINCT...) WHERE ROWNUM <=3) examples that people suggested
    - However, you must also make sure that those 3 states are actually ones that will appear in the final results set. It would do you no good to pick California, for example, in that initial list, since it does not appear in the final list.


    Therefore, your initial list must include all the restrictions of the final query. Look at this:

    SELECT
    ---IM.COD_RED,
    ---IM.COD_AGE,
    ---IM.COD_PRM
    FROM
    ---(
    ------SELECT
    ---------*
    ------FROM
    ---------(
    ------------SELECT DISTINCT
    ---------------IM.COD_RED,
    ---------------IM.COD_AGE,
    ---------------IM.COD_PRM
    ------------FROM
    ---------------IC_MODELOS------IM,
    ---------------IC_PROMOCIONES---IP
    ------------WHERE
    ---------------IP.COD_PAIS---------=---2---AND
    ---------------IP.COD_COM---------=---15---AND
    ---------------IP.COD_PRV---------=---28---AND
    ---------------IM.COD_TIP_INMU---=---8---AND
    ---------------IM.COD_MON---------=---1
    ---------------IP.COD_RED---------=---IM.COD_RED---------AND
    ---------------IP.COD_AGE---------=---IM.COD_AGE---------AND
    ---------------IP.COD_PRM---------=---IM.COD_PRM---------AND
    ---------)
    ------WHERE
    ---------ROWNUM <= 3
    ---)---T1
    ---IC_MODELOS------IM,
    ---IC_PROMOCIONES---IP,
    ---IC_EMPRESA------IE,
    ---IC_POBLACION---IPa,
    ---IC_TIPINMUEB---IT,
    ---IC_SBTINMUEB---ISb,
    ---IC_MONEDA------IMn
    WHERE
    ---IP.COD_PAIS---------=---2------------------AND
    ---IP.COD_COM---------=---15------------------AND
    ---IP.COD_PRV---------=---28------------------AND
    ---IM.COD_TIP_INMU---=---8------------------AND
    ---IM.COD_MON---------=---1------------------AND
    ----- -------------------------------
    ---IM.COD_RED---------=---T1.COD_RED---------AND
    ---IM.COD_AGE---------=---T1.COD_AGE---------AND---
    ---IM.COD_PRM---------=---T1.COD_PRM---------AND
    ----- -------------------------------
    ---IP.COD_RED---------=---IM.COD_RED---------AND
    ---IP.COD_AGE---------=---IM.COD_AGE---------AND
    ---IP.COD_PRM---------=---IM.COD_PRM---------AND
    ---IMn.COD_MON---------=---IM.COD_MON---------AND
    ---IPa.COD_POB---------=---IP.COD_POB---------AND
    ---IT.COD_TIP_INM------=---IM.COD_TIP_INMU---AND
    ---ISb.COD_SBT_INM---=---IM.COD_SBT_INMU---AND
    ---IE.COD_AGE---------=---IP.COD_AGE


    Now, I have assumed 2 things here:

    - The IE, IPa, etc. tables are *non-restrictive*. In other words, they are simply used to provide additional fields to the result set, but do not remove any rows from the result set. *If this assumption is incorrect*, then you need to add those tables to the inner-select as well.

    - I am also assuming that you have more in your SELECT list than you are showing us, otherwise, this entire exercise would not make sense. Going back to your original example, it would not make sense to go through all these hoops if you were only displaying the State column. You need at least a single detail column as well for all this to make sense. I'm assuming you chopped those columns out of the SELECT list for simplicity.

    Anyway, give this a shot and let me know what you find.

    - Chris

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