CODEWITHSUNDEEP
mysqlsqloracle-databasehivehiveql
Babu
Babu

Reputation: 891

Hive query to get min and max of date column group by year and id

I have table with two columns ,id and date.I want to get the max and min dates for that particular id as per that year. Below is the sample data and the result I wanted Sample Data

id  date_col

123 2015-05-01 04:00:00

123 2017-04-01 04:00:00

123 2017-09-01 04:00:00

123 2014-09-01 04:00:00

123 2012-12-01 05:00:00

123 2016-08-01 04:00:00

123 2014-05-01 04:00:00

123 2016-10-01 04:00:00

Results I am expecting

123 2014-05-01   2014-09-01

123 2015-05-01

123 2016-08-01   2016-10-01

123 2017-04-01   2017-09-01

I tried by using below multiple query but i won't be sorting according to the year

SELECT id,MAX(date_col) AS maxdate_col,MIN(date_col) AS mindate_col FROM table GROUP BY id 

SELECT id,MAX(date_col) AS max_votes,MIN(date_col) AS mindate_col,YEAR(date_col) FROM test GROUP BY id,YEAR(date_col)

Upvotes: 1

Views: 5180

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Try this

SELECT id
        ,MIN(date_col) AS mindate_col
        ,(CASE WHEN MAX(date_col) <> MIN(date_col) THEN MAX(date_col) END)  AS maxdate_col
        ,YEAR(date_col) AS year_col
FROM    test 
GROUP BY id,YEAR(date_col)
ORDER BY id,year_col

If you want only the date part, you can cast it CAST(date_col AS DATE)

Upvotes: 1

Related Questions