CODEWITHSUNDEEP
sqloracle-database
Amjad AL-Ahdal
Amjad AL-Ahdal

Reputation: 39

The Maximum value of two columns with group by

I have a table that contains the followings data : 

TRIP   TRIP_DATE    TRIP_TIME
A      2018-08-08   11:00
A      2018-08-09   11:00
A      2018-08-08   23:00
A      2018-08-20   11:00
A      2018-08-20   14:00

I want the select statement to retrieve the Number of trips, Count , the latest date and time. Basically the output should be like this: 

TRIPS   MAX(TRIP_DATE)    TRIP_TIME
5       2018-08-20        14:00

Upvotes: 0

Views: 161

Answers (5)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following using GROUP BY:

SELECT TRIP, COUNT(TRIP) AS cnt, MAX(CONCAT(TRIP_DATE, ' ', TRIP_TIME)) AS maxDateTime 
FROM table_name
GROUP BY TRIP

To combine the DATE and TIME value you can use one of the following:

  • using CONCAT_WS: CONCAT_WS(' ', TRIP_DATE, TRIP_TIME)
  • using CONCAT: CONCAT(TRIP_DATE, ' ', TRIP_TIME)

You can use the above query as sub-query to get the DATE and TIME as seperate values:

SELECT TRIP, cnt, DATE(maxDateTime), TIME_FORMAT(TIME(maxDateTime), '%H:%i') FROM (
   SELECT TRIP, COUNT(TRIP) AS cnt, MAX(CONCAT(TRIP_DATE, ' ', TRIP_TIME)) AS maxDateTime 
   FROM table_name
   GROUP BY TRIP
)t;

Note: I recommend to split the DATE and TIME values on the application side. I would also store the DATE and TIME value in one column as DATETIME instead of separate columns.

demos: https://www.db-fiddle.com/f/xcMdmivjJa29rDhHxkUmuJ/2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is tricky. I think I would do:

select cnt, date, time
from (select t.*,
             row_number() over (partition by trip order by date desc, time desc) as seqnum
             count(*) over (partition by trip) as cnt
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Radhe Shyam Courasiya
Radhe Shyam Courasiya

Reputation: 96

You have option of using analytic function as will as group function here. All will do the job . Looking at final output I believe max function with group by is more suitable. There is no hard and fast rule but personally I prefer grouping when final outcome need to be suppressed.

Upvotes: 0

ccarpenter32
ccarpenter32

Reputation: 1077

I would go with this (assuming you wanted the MAX Trip_Time as well, its a little difficult to tell from your example):

SELECT COUNT(TRIP) AS Trips, 
MAX(TRIP_DATE) AS MAX(TRIP_DATE), 
MAX(TRIP_TIME) AS TRIP_TIME 
FROM myTable
GROUP BY TRIP

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() function :

select t.*
from (select *, row_number() over (partition by trip order by date desc, time desc) seq
      from table t
     ) t
where seq = 1;

Upvotes: 0

Related Questions