CODEWITHSUNDEEP
sqloracle-databasedatabase-design
eniacAvenger
eniacAvenger

Reputation: 875

SQL Query from list of strings

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;

Upvotes: 0

Views: 4950

Answers (4)

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

Reputation: 50027

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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;

Upvotes: 1

user3278460
user3278460

Reputation:

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

Upvotes: 1

Siyual
Siyual

Reputation: 16917

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;

Upvotes: 0

Related Questions