How to create a sql query get the 2nd to Max result in table?-CodePudding

I have a table in snowflake and I want to see the results for the max date and 2nd to Max date. I wrote a query below which gives the results for max date

SELECT MAX(FILE_DATE)  "MAX_DATE"
    ,UPPER(RETAIL)  "SHOP"
FROM PRODUCTS
GROUP BY UPPER(RETAIL)

the results are something like this

MAX_DATE   SHOP
2022-09-27 SHOP1
2022-08-01 SHOP2

I am looking for

2nd_MAX_DATE MAX_DATE   SHOP
2022-08-27   2022-09-27 SHOP1
2022-07-01   2022-08-01 SHOP2

I tried something with where clause WHERE FILE_DATE < ( SELECT MAX(FILE_DATE) FROM "PRODUCTS" GROUP BY UPPER(RETAILER)) but this isn't working

CodePudding user response：

One way is to use a cross join.

Here's a full sample roughly altered to your schema using SQL Server syntax.

CREATE TABLE Products (
  Id INT PRIMARY KEY, 
  Retail VARCHAR(100), 
  FileDate DATETIME
);

INSERT INTO Products (Id, Retail, FileDate)
VALUES (1, 'shop 1', '2022-09-01'), 
       (2, 'shop 1', '2022-09-01'),
       (3, 'shop 1', '2022-09-01'),
       (4, 'shop 2', '2022-09-01'),
       (5, 'shop 2', '2022-09-01'),
       (6, 'shop 2', '2022-09-30'),
       (7, 'shop 3', '2022-09-01'),
       (8, 'shop 3', '2022-09-01'),
       (9, 'shop 3', '2022-09-20');

SELECT DISTINCT
    MAX(p1.FileDate) "MAX_DATE",
    UPPER(p1.Retail) "SHOP"
FROM Products p1
    CROSS JOIN Products p2
WHERE
    p1.FileDate > p2.FileDate
GROUP BY 
    p1.Retail,
    p2.Retail

Output

2022-09-30|SHOP 2
2022-09-20|SHOP 3

CodePudding user response：

As you want the latest two file_dates for every shop, you can use WINDOW function

WITH CTE as(
SELECT FILE_DATE  
    ,UPPER(RETAIL)  "SHOP"
    , ROW_NUMBER() OVER(PARTITION BY UPPER(RETAIL) ORDER BY FILE_DATE DESC) rn
FROM PRODUCTS
)
SELECT "SHOP",MIN(FILE_DATE) second_date, MAX(FILE_DATE) first_date
FROM CTE 
WHERE rn < 3 
GROUP BY "SHOP"

CodePudding user response：

create or replace TABLE SNOWPARK_QUICKSTART.TELCO.Products ( ID INT, FileDate TIMESTAMP, Retail VARCHAR(16777216) );

INSERT INTO Products (Id, Retail, FileDate)
VALUES (1, 'shop 1', '2022-09-01'), 
   (2, 'shop 1', '2022-09-01'),
   (3, 'shop 1', '2022-09-01'),
   (4, 'shop 2', '2022-09-01'),
   (5, 'shop 2', '2022-09-01'),
   (6, 'shop 2', '2022-09-30'),
   (7, 'shop 3', '2022-09-01'),
   (8, 'shop 3', '2022-09-01'),
   (9, 'shop 3', '2022-09-20');


SELECT RETAIL
      ,FILEDATE
      ,IFF(RANK()OVER(PARTITION BY RETAIL ORDER BY FILEDATE DESC)=2,'SECOND MAX DATE','MAX DATE') DAY_DESC  
FROM 
    Products 
GROUP BY 
    RETAIL,FILEDATE
QUALIFY 
    RANK()OVER(PARTITION BY RETAIL ORDER BY FILEDATE DESC)<3