Skip to content

Instantly share code, notes, and snippets.

@YiLi225

YiLi225/max_value.sql

Created March 9, 2020 16:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save YiLi225/7ddfb1c91be65bb912725f845757eaef to your computer and use it in GitHub Desktop.
Save YiLi225/7ddfb1c91be65bb912725f845757eaef to your computer and use it in GitHub Desktop.
Download ZIP
Raw
max_value.sql
--- 3) Find the record having a number calculated by analytic functions (e.g., MAX) without self-joining
SELECT *
FROM
(
SELECT
DAT.*,
CASE WHEN (NUM_VAR = MAX(NUM_VAR) OVER (PARTITION BY ID_VAR)) THEN 'Y' ELSE 'N' END AS MAX_NUM_IND
FROM
CURRENT_TABLE DAT
) DAT2
WHERE MAX_NUM_IND = 'Y'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment