SQL syntax error while trying to create a new column with an if/else clause

Question

I am using this schema in PostgreSQL v9.6:

CREATE TABLE dates (
  calendar_week_start timestamp,
  calendar_week_end timestamp,
  calendar_year integer,
  calendar_year_category varchar(255)
);

INSERT INTO dates (calendar_week_start, calendar_week_end, calendar_year, calendar_year_category)
VALUES ((DATE'2018-01-01'), (DATE'2018-01-07'), 2018, 'Prior Year'), 
       ((DATE'2018-01-08'), (DATE'2018-01-14'), 2018, 'Prior Year'), 
       ((DATE'2018-01-15'), (DATE'2018-01-21'), 2018, 'Prior Year'),
       ((DATE'2018-01-22'), (DATE'2018-01-28'), 2018, 'Prior Year'),
       ((DATE'2019-01-01'), (DATE'2019-01-07'), 2019, 'Current Year'), 
       ((DATE'2019-01-08'), (DATE'2019-01-14'), 2019, 'Current Year'), 
       ((DATE'2019-01-15'), (DATE'2019-01-21'), 2019, 'Current Year'),
       ((DATE'2019-01-22'), (DATE'2019-01-28'), 2019, 'Current Year');

And I am attempting to run this query to create a column called "latest_week" that should check both the calendar_week_end and calendar_year_category fields to determine whether the date is the latest week in the table or not, and assign a value of 'Yes' or 'No'.

Whenever I try a "dummy" query comparing two rows that exist, the query runs as expected. Whenever I try to use MAX(calendar_week_end) to compare the date values to the maximum date value, the queries start throwing errors.

Below is one such query that returns an unexpected error (with no other information given).

SELECT calendar_week_end GROUP BY calendar_year_category,
    CASE
        WHEN calendar_week_end=MAX(calendar_week_end) THEN 'Yes'
        ELSE 'No'
    END
    AS latest_week
FROM dates;

If I run this "dummy" query simply comparing calendar_week_start and calendar_week_end, however, the same syntax works and I get the new column latest_week with all values as 'No' as expected.

SELECT calendar_week_start, calendar_year_end GROUP BY calendar_week_category,
    CASE
        WHEN calendar_week_start=calendar_week_end THEN 'Yes'
        ELSE 'No'
    END
    AS latest_week
FROM dates;

What is the proper syntax if I want to use MAX(calendar_week_end) in my query?

Unrelated, but: putting parentheses around a single date value (DATE'2018-01-22') is completely useless — user330315
– user330315, Commented Jul 17, 2019 at 18:45

Gordon Linoff · Accepted Answer · 2019-07-16 18:18:08Z

2

You can use a window function. The following creates a boolean, rather than a string:

SELECT d.*,
       ( 1 = row_number() over (order by calendar_week_end desc) ) as is_latest_week
FROM dates d;

answered Jul 16, 2019 at 18:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL syntax error while trying to create a new column with an if/else clause

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related