Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    27

    Unanswered: subqueris and order by

    Hello all,

    I am trying to run this kinda query:
    The idea: to get the top 3 for each TKG_NAME
    using order by TRU

    problem: subqueries not supporting order by
    Any ideas on how to over come the problem (without using temporary table
    or cursors)

    Here is my select (which not working )
    Thanks
    Chanan

    SELECT N1.SWITCH_NAME,
    N1.CARRIER,
    N1.CARRIER_CLASS,
    N1.TKG_NAME,
    N1.TRU
    FROM pmmcounter_db..NOR_TRK_H as N1
    WHERE N1.PMM_DATETIME>=dateadd(hh,-30,getdate())
    AND N1.CLASSIFICATION ='Off-Net'
    AND N1.TKG_NAME in ( SELECT N2.TKG_NAME
    FROM pmmcounter_db..NOR_TRK_H as N2
    WHERE N2.TKG_NAME = N1.TKG_NAME
    AND N2.PMM_DATETIME>=dateadd(hh,-30 ,getdate())
    AND N2.CLASSIFICATION ='Off-Net'
    ORDER BY N2.TRU
    )

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It's difficult to provide a correct solution because you don't really describe what your code is supposed to do while the table and field names are pretty meaningless to. This means we have to try and work out what your code is supposed to do from unformatted SQL that doesn't work.

    I'm sure it won't be difficult providing a solution but we need better info. You should probably use a correlated sub-query that uses count ie select switches where there are less than 3 better switches for that tkg_name but without more information it's difficult to say.

    PS: To keep the indenting you can highlight your SQL and then press the # button.

  3. #3
    Join Date
    Dec 2009
    Posts
    27

    some more information

    some more information:
    I got a table where there are TKG_NAME and TRU values
    where I look to find the top 3 TRU values for each TKG_NAME
    using where statement in sepecific time and with specific classification.

    I tried bringing the subquery the top X TRU values for each TKG_NAME
    in the specific time and classification, and then match it to the current TKG_NAME found - but it fails due to order by in the subquery (which is restricted according to sybase documentation)

    Code:
    SELECT N1.SWITCH_NAME, 
               N1.CARRIER,
               N1.CARRIER_CLASS,
               N1.TKG_NAME,
               N1.TRU
      FROM pmmcounter_db..NOR_TRK_H as N1
     WHERE N1.PMM_DATETIME>=dateadd(hh,-30,getdate())
         AND N1.CLASSIFICATION ='Off-Net'
         AND N1.TKG_NAME in ( SELECT N2.TKG_NAME
                                           FROM pmmcounter_db..NOR_TRK_H as N2
                                          WHERE N2.TKG_NAME = N1.TKG_NAME
                                              AND N2.PMM_DATETIME>=dateadd(hh,-30                                                ,getdate())
                                               AND N2.CLASSIFICATION ='Off-Net'
                                          ORDER BY N2.TRU
                                     )

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Something along these lines might work - you'd have to try it out and see what it generates. I obviously can't test it.
    Code:
    SELECT N1.SWITCH_NAME, 
           N1.CARRIER,
           N1.CARRIER_CLASS,
           N1.TKG_NAME,
           N1.TRU
    FROM   pmmcounter_db..NOR_TRK_H as N1
    WHERE  N1.PMM_DATETIME>=dateadd(hh,-30,getdate())
           AND N1.CLASSIFICATION ='Off-Net'
           and (
              select count(*)
              from   pmmcounter_db..NOR_TRK_H as N2
              WHERE  N2.TKG_NAME = N1.TKG_NAME
                     AND 2.PMM_DATETIME>=dateadd(hh,-30 ,getdate())
                     AND N2.CLASSIFICATION ='Off-Net'
                     AND N2.TRU < N1.TRU ) < 3
    Mike

Posting Permissions

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