This discussion is archived
8 Replies Latest reply: Aug 16, 2012 4:37 AM by kendenny RSS

Tricky SQL Query.. help

956053 Newbie
Currently Being Moderated
Hi all,
I am new with this forum, will be here often. i have a question about sq query that i need to run here.

OK. the table

customer
CID_ID CREATE_DT
-------------------- ---------
0000000029003242 10-JUN-12
0000000029003074 10-JUN-12
0000000029003191 10-JUN-12
0000000029002097 09-JUN-12
0000000029004443 12-JUN-12
0000000028975367 10-JUN-12
0000000029004178 11-JUN-12
0000000028998641 07-JUN-12
0000000029003191 10-JUN-12
0000000028998641 07-JUN-12

10 rows selected

the question :
ok, for the result i wanted 2 column which is [cid_id, count(cid_id)] and it must be ordered by create_dt. i know it need group by and order by. i'm ok with using group by or order by them self, but i couldn't get these running at the same time beautifully.

anybody please help me, i have workied on this for 3 days already still i cannot see how i can do this.
  • 1. Re: Tricky SQL Query.. help
    956053 Newbie
    Currently Being Moderated
    this is the original script which is NOT order by create_dt

    SELECT cid_id, COUNT(cid_id)
    FROM CUSTOMER
    GROUP BY CID_ID;
  • 2. Re: Tricky SQL Query.. help
    Manik Expert
    Currently Being Moderated
    Did u try something like this?
    WITH tmp AS
            (SELECT '0000000029003242' cid_id, '10-JUN-12' create_dt FROM DUAL
             UNION ALL
             SELECT '0000000029003074', '10-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000029003191', '10-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000029002097', '09-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000029004443', '12-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000028975367', '10-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000029004178', '11-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000028998641', '07-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000029003191', '10-JUN-12' FROM DUAL
             UNION ALL
             SELECT '0000000028998641', '07-JUN-12' FROM DUAL)
      SELECT create_dt, COUNT (cid_id)
        FROM tmp
    GROUP BY create_dt;
  • 3. Re: Tricky SQL Query.. help
    Khayyam Explorer
    Currently Being Moderated
    WITH t1 AS (SELECT '0000000029003242' cid_id, '10-JUN-12' create_dt FROM DUAL
                UNION ALL
                SELECT '0000000029003074', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029003191', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029002097', '09-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029004443', '12-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028975367', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029004178', '11-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028998641', '07-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029003191', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028998641', '07-JUN-12' FROM DUAL)
      SELECT cid_id, COUNT (cid_id)
        FROM (  SELECT *
                  FROM t1
              ORDER BY create_dt)
    GROUP BY cid_id
    for the result i wanted 2 column which is [cid_id, count(cid_id)] and it must be ordered by create_dt.
    But this doesnt makes any sence... You grouping data.. and you can order only column you got in output... ORDER BY works in the end of execution of statement... In this case you can only order by cid_id and COUNT (cid_id)...

    If your requirement more specefic please clarify for us and we will try to do something...
  • 4. Re: Tricky SQL Query.. help
    UW (Germany) Explorer
    Currently Being Moderated
    In your example the records with the same CID_ID have the same CREATE_DT, but that must not always be true.
    So when you want to order the results, you can order by the first or last date of all records, that have the same CID_ID like:
    WITH tmp AS
            (SELECT '0000000029003242' cid_id, '10-JUN-12' create_dt FROM DUAL  UNION ALL
             SELECT '0000000029003074', '10-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000029003191', '10-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000029002097', '09-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000029004443', '12-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000028975367', '10-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000029004178', '11-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000028998641', '07-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000029003191', '10-JUN-12' FROM DUAL UNION ALL
             SELECT '0000000028998641', '07-JUN-12' FROM DUAL)
      SELECT cid_id, COUNT (cid_id)
        FROM tmp
    GROUP BY cid_id
    order by min(create_dt);  -- or max respectively
    If your application ensures, that records with same CID_ID have the same date, min or max will be return the same result.
  • 5. Re: Tricky SQL Query.. help
    hm Expert
    Currently Being Moderated
    Is it something like this you are looking for?
    -- Test-data:
    WITH customer AS
            (SELECT '0000000029003242' cid_id, to_date('10-JUN-12','DD-MON-YY') create_dt FROM DUAL
             UNION ALL
             SELECT '0000000029003074', to_date('10-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000029003191', to_date('10-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000029002097', to_date('09-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000029004443', to_date('12-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000028975367', to_date('10-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000029004178', to_date('11-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000028998641', to_date('07-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000029003191', to_date('10-JUN-12','DD-MON-YY') FROM DUAL
             UNION ALL
             SELECT '0000000028998641', to_date('07-JUN-12','DD-MON-YY') FROM DUAL)
    -- Query:
    select cid_id, cnt
    from         
    (         
      SELECT cid_id, create_dt, COUNT (cid_id) cnt
        FROM customer
    GROUP BY cid_id, create_dt
    )
    order by create_dt, cid_id;
    anoter variant could be this:
    select cid_id, cnt
    from         
    (         
      SELECT cid_id, min(create_dt) min_cd, COUNT (cid_id) cnt
        FROM customer
    GROUP BY cid_id, create_dt
    )
    order by min_cd, cid_id;
    In the first variant your would get one line per cid_id and create_dt (if that is possible).
    In the second variant you would get only one line per cid_id (ordered by the first create_dt for that cid_id).

    When the create_dt is unique for one cid_id both queries will produce the same results.

    Edited by: hm on 16.08.2012 00:05
  • 6. Re: Tricky SQL Query.. help
    956053 Newbie
    Currently Being Moderated
    yes!!!! this is what i wanted!!! now i understand why it cannot "order by" because it doesn't know which create_dt to take ( if i got 2 or more create_dt per cid_id)!!!
    so what we need to do is to tell sql order it by the earliest create_dt... yes!!!!! thank you!!!!

    thank you Sir UW (Germany) , you saved my ass!!!!! owe you big time!!!!
  • 7. Re: Tricky SQL Query.. help
    indra budiantho Expert
    Currently Being Moderated
    /* Formatted on 8/16/2012 2:51:53 PM (QP5 v5.139.911.3011) */
    WITH tmp
            AS (SELECT '0000000029003242' cid_id, '10-JUN-12' create_dt FROM DUAL
                UNION ALL
                SELECT '0000000029003074', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029003191', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029002097', '09-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029004443', '12-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028975367', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029004178', '11-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028998641', '07-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000029003191', '10-JUN-12' FROM DUAL
                UNION ALL
                SELECT '0000000028998641', '07-JUN-12' FROM DUAL)
    SELECT cid_id, create_dt, cnt
      FROM (SELECT cid_id,
                   create_dt,
                   COUNT (
                      cid_id)
                   OVER (PARTITION BY cid_id
                         ORDER BY TO_DATE (create_dt, 'dd-Mon-yy'))
                      cnt,
                   ROW_NUMBER ()
                   OVER (PARTITION BY cid_id
                         ORDER BY TO_DATE (create_dt, 'dd-Mon-yy'))
                      rn
              FROM tmp)
     WHERE rn = 1
    0000000028975367     10-JUN-12     1
    0000000028998641     07-JUN-12     2
    0000000029002097     09-JUN-12     1
    0000000029003074     10-JUN-12     1
    0000000029003191     10-JUN-12     2
    0000000029003242     10-JUN-12     1
    0000000029004178     11-JUN-12     1
    0000000029004443     12-JUN-12     1

    Edited by: Indra Budiantho on Aug 16, 2012 12:53 AM
  • 8. Re: Tricky SQL Query.. help
    kendenny Expert
    Currently Being Moderated
    How can you order by create_dt when you don't group by create_dt? You have 20 rows with a given cid_id which are all grouped together but each one has a different create_dt. Which one do you want to use to do the ordering? Maybe the MIN?
    select cid_id, count(*) from customer
     group by cid_id
     order by min(create_dt);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points