Store calendar week and year in one (date) column in SQL-CodePudding

My table includes two columns: calendar week and year. If I want to get the latest entries by calendar week and year, I currently perform:

SELECT * FROM table WHERE calyear = (SELECT MAX(calyear) FROM table) AND calweek = (SELECT MAX(calweek) FROM table WHERE calyear = (SELECT MAX(calyear) FROM table))

which is super long. I'd like to replace this with a combination of week and year e.g. 'calweek-calyear' column. Is there a date format for that or should I save this as a tiny text? I want to be able to perform MAX() on it and performance shouldn't suffer singificantly. Im open for better solutions, thanks.

CodePudding user response：

Your super long query can be simplified to:

SELECT * 
FROM tablename
ORDER BY calyear DESC, calweek DESC
LIMIT 1;

if you expect only 1 row as a result.

If there are more than 1 rows for the max calyear and calweek combination, you could use RANK() window function:

SELECT t.*
FROM (
  SELECT *, RANK() OVER (ORDER BY calyear DESC, calweek DESC) rnk
  FROM tablename
) t
WHERE t.rnk = 1;

Also, I would advice against the use of a combination of year and week.
Keep your data as simple as possible.
For presentation purposes you could easily concatenate the 2 columns.

CodePudding user response：

If you concatenate YYYYWW in a column TINYTEXT, or other text type I think it will do what you want. If you make sure that your week numbers are 2 digit ie 01 and not 1 you could use INT. I would rather advise the use of a column DATE and a modified query.