CODEWITHSUNDEEP
sql
Vinoth96
Vinoth96

Reputation: 353

Need to Find Min & Max date specific status column value in SQL

Below table Product:

+--------------+-----------+--------+-----------+
| Product_code | Prod Name | Status |   Date    |
+--------------+-----------+--------+-----------+
| A            | AA1       | New    | 12/3/2019 |
| A            | AA2       | Expiry | 7/20/2017 |
+--------------+-----------+--------+-----------+

expected output:

+--------------+-----------+------------+-----------+------------+------------+
| Product_code | Min_date  | Min_status | Max_date  | Max_status | count_Prod |
+--------------+-----------+------------+-----------+------------+------------+
| A            | 7/20/2017 | Expiry     | 12/3/2019 | New        |          2 |
+--------------+-----------+------------+-----------+------------+------------+

Tried with below query

SELECT Product_code,MIN(Date) as "Min_date", MAX(Date) as 
"Max_date",count(Prod_Name) as "count_Prod"
FROM Product where Product_code ='A'  
GROUP BY Product_code
ORDER BY Product_code;

Need a help to pull the specific status for date column.

Upvotes: 0

Views: 526

Answers (1)

Jim Macaulay
Jim Macaulay

Reputation: 5155

Please use below query. You can use Min and Max function for string column as well. It aggregates based on the alphabetical order.

SELECT Product_code,MIN(Date) as "Min_date", MIN(Status) as Min_Status,  MAX(Date) as 
"Max_date", MAX(Status) as Max_Status,count(Prod_Name) as "count_Prod"
FROM Product where Product_code ='A'  
GROUP BY Product_code
ORDER BY Product_code;

Upvotes: 1

Related Questions