8 Replies Latest reply: Aug 16, 2012 6:37 AM by kendenny RSS

    Tricky SQL Query.. help

    956053
      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
          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
            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
              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)
                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
                  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
                    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
                      /* 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
                        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);