SQL Query from list of strings

Question

I am trying to query the last time a file was imported from a SQL table "import", given a month string integer (Jan is '01', Feb is '02', March is '03'..). I have pasted my solution below but I was wondering if there is a more elegant way of doing so.

SELECT DISTINCT 
months.month_string, MAX(import.process_date)
FROM import import,
(
    select '01' month_string from dual union
    select '02' month_string from dual union
    select '03' month_string from dual union
    select '04' month_string from dual union
    select '05' month_string from dual union
    select '06' month_string from dual union
    select '07' month_string from dual union
    select '08' month_string from dual union
    select '09' month_string from dual union
    select '10' month_string from dual union
    select '11' month_string from dual union
    select '12' month_string from dual 
) months
WHERE import.process_month (+) = months.month_string
GROUP BY months.month_string
ORDER BY months.month_string;

Is there ever anything other than '01' - '12' for the month_string? — Siyual
– Siyual, Commented Apr 28, 2014 at 20:37

Bob Jarvis - Слава Україні · Accepted Answer · 2014-04-29 14:29:13Z

2

How about

WITH SUMMARY_DATA AS
  (SELECT CASE
            WHEN PROCESS_MONTH IN ('01', '02', '03', '04', '05', '06',
                                   '07', '08', '09', '10', '11', '12')
              THEN PROCESS_MONTH
            ELSE
               NULL
          END AS SUMMARY_MONTH,
          PROCESS_DATE
     FROM IMPORT)
SELECT SUMMARY_MONTH, MAX(PROCESS_DATE)
  FROM SUMMARY_DATA
  GROUP BY SUMMARY_MONTH
  ORDER BY SUMMARY_MONTH

SQLFiddle here

Share and enjoy.

edited Apr 29, 2014 at 14:29

answered Apr 29, 2014 at 1:11

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

eniacAvenger Over a year ago

Just tested this and it works great. In the future if I had a large list of strings, could you use a PL/SQL statement uses an array in the "IN" parentheses?

Gordon Linoff Over a year ago

This answer confuses me because it will not show a summary month if there is no data present for that month. That is why a months table would be used.

Bob Jarvis - Слава Україні Over a year ago

@GordonLinoff - NULL seems to work fine as a 'default' or 'dummy' value for SUMMARY_MONTH in the subquery factoring clause. Please see this SQLFiddle. Share and enjoy.

Gordon Linoff Over a year ago

@BobJarvis . . . The original query uses a left outer join, presumably to get a row for 12 months, even if data is not present. The SQL Fiddle returns 3 rows, not 12.

Bob Jarvis - Слава Україні Over a year ago

@GordonLinoff - ah, now I see what you're driving at. Yes, you're correct, my answer doesn't do that. I'd delete the answer as it doesn't seem to satisfy the original requirements, but OP's comment above seems to indicate it's acceptable so I guess I'll let it stand. Thanks.

|

Gordon Linoff · Accepted Answer · 2014-04-29 14:01:42Z

1

I don't know if you will find this more "elegant", but here is a better way to write the query:

SELECT months.month_string, MAX(import.process_date)
FROM (select '01' as month_string from dual union all
      select '02' as month_string from dual union all
      select '03' as month_string from dual union all
      select '04' as month_string from dual union all
      select '05' as month_string from dual union all
      select '06' as month_string from dual union all
      select '07' as month_string from dual union all
      select '08' as month_string from dual union all
      select '09' as month_string from dual union all
      select '10' as month_string from dual union all
      select '11' as month_string from dual union all
      select '12' as month_string from dual 
     ) months LEFT OUTER JOIN
     import
     on import.process_month = months.month_string
GROUP BY months.month_string
ORDER BY months.month_string;

Here are the changes:

Replaced the (uninterpretable) Oracle syntax for outer joins with an explicit, ANSI standard outer join.
Reversed the order of the tables, to use a left outer join rather than a right outer join.
Changed select distinct to select. select distinct is almost never needed with group by.
Changed union to union all. union expends effort to remove duplicates, which is not needed.
Added as for the column aliases. This makes it more apparent that the name is being assigned to the column, and helps prevent wandering commas from messing up the query.

You could also use a connect by or recursive CTE to actually generate the month numbers, but I'm not sure that would be as clear as this version.

EDIT:

I was making the assumption that you need to get NULL values out because not all months would be present in import. That is why you would use a months table. If not, just do:

SELECT i.process_month, MAX(i.process_date)
FROM import i
GROUP BY i.process_month
ORDER BY i.process_month;

If you are concerned about the range,

SELECT i.process_month, MAX(i.process_date)
FROM import i
WHERE i.process_month in ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10',
                          '11', '12'
                         )
GROUP BY i.process_month
ORDER BY i.process_month;

edited Apr 29, 2014 at 14:01

answered Apr 28, 2014 at 20:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Hogan Over a year ago

wouldn't be faster to do a sub-query that groups by import.process_month and takes the max and then join that result to the composed table?

eniacAvenger Over a year ago

Thanks for the reminder of not using the depricated oracle syntax for joins. I will have to research this "union all", I have only used union in the past.

Bob Jarvis - Слава Україні Over a year ago

@eniacAvenger - UNION checks to make sure there are no duplicates in the results, and can run very slowly because of this. UNION ALL doesn't check for duplicates and will often run much faster. Share and enjoy.

eniacAvenger Over a year ago

@BobJarvis sorry about the accepting/unaccepting of the answer, I am new to stackoverflow and thought that I could accept more than one answer. I have selected this answer since it will solve the problem in all scenarios.

Bob Jarvis - Слава Україні Over a year ago

@eniacAvenger - I honestly hadn't noticed anything so no problem here. :-) Welcome to Stack Overflow!

user3278460 · Accepted Answer · 2014-04-29 08:51:16Z

1

You can use all_objects for counter, distinct is not neccessary in this query

select months.month_string, MAX(import.process_date)
from import import, 
     (select lpad(to_char(rownum), 2, 0) month_string from all_objects where rownum <= 12) months
where  import.process_month (+) = months.month_string
group by months.month_string
order by months.month_string

answered Apr 29, 2014 at 8:51

user3278460

1 Comment

eniacAvenger Over a year ago

Thanks, this is also an interesting solution to the problem. Just tested and it works.

Siyual · Accepted Answer · 2014-04-28 20:41:25Z

0

Since there aren't any other values for the process_month you can accomplish this without the implicit Join to a months table.

Perhaps the following will work?

SELECT  process_month, MAX(process_date)
FROM    import
GROUP BY process_month
ORDER BY process_month;

answered Apr 28, 2014 at 20:41

Siyual

17k8 gold badges47 silver badges60 bronze badges

1 Comment

eniacAvenger Over a year ago

There could be a month that does not have import data, and I need the results to show up as null if this happens. Therefore I need to feed it a list of month strings. But thanks for the suggestion.

Collectives™ on Stack Overflow

SQL Query from list of strings

4 Answers 4

7 Comments

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related