Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: How to get the count of records on this query?

    I need the number of records returned by this query:

    Code:
    select distinct(M_TRADENUMB) from DM_RSK_CR_PS_R_REP where M_Z_DAYOFRUN='FIRST RUN' HAVING M_MX_REF_JOB=max(M_MX_REF_JOB);
    Unfortunately, simply wrapping it in count(): `select count(distinct(M_TRADENUMB)) ...' does not work...

    The only way we found so far is via a subquery -- figure out the max(M_MX_REF_JOB) first:
    Code:
    select COUNT(DISTINCT(M_TRADENUMB)) from DM_RSK_CR_PS_R_REP WHERE
        M_MX_REF_JOB = (SELECT MAX(M_MX_REF_JOB) FROM DM_RSK_CR_PS_R_REP WHERE M_Z_DAYOFRUN = 'FIRST RUN');
    but I dislike the subqueries...

    Can this be done while avoiding the subqueries? Am I right disliking them, or is the alternative I'm struggling to come up with just as hard for the server to run?

    Thanks!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm not sure if you'll like this any better but it should be efficeint(ish)
    Code:
    select   count(*)
    from     (
               select    distinct(M_TRADENUMB) as trdnum
               from      DM_RSK_CR_PS_R_REP 
               where    M_Z_DAYOFRUN='FIRST RUN' 
               HAVING M_MX_REF_JOB=max(M_MX_REF_JOB)
               ) tmp
    PS if you highlight code and then press the # button it will format it like above which is easier to read.

    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
  •