Select rows with MAX date for each logday?

[marcarls]

Hi
I have a log table like this:
Case_id Text_id Log_time
100 2 2003-01-01 15:00
100 2 2003-01-01 16:00
110 5 2003-04-09 10:00
110 5 2003-04-09 11:00

I would like to write a query that returns 2 rows that is for every case_id and text_id I want the last logged row for that day, and not all the rows.
And using distinct do not work since all four rows are distinct.

Any suggestions?

Regards Maria

 

[JamesLean]

SELECT case_id, text_id, MAX(log_time)
FROM t
GROUP BY case_id, text_id

--James

 

[marcarls]

Thanks for your answer. That do not work since max is not a single group function. However I figured out a soulution like this

SELECT log_1.case_id
, log_1.text_id
, log_1.logtime
FROM log log_1
WHERE to_char(log_1.logtime, 'YYYY-MM-DD HH24:MI:SS') =
(SELECT to_char(max(log_2.logtime), 'YYYY-MM-DD HH24:MI:SS')
FROM log log_2
WHERE log_1.case_id = log_2.case_id
AND log_1.text_id = log_2.text_id

Regards Maria

 

[bakaith]

Is there any way to get the second highest for the same scenario pointed out above? TIA.

 

[swampBoogie]

SELECT case_id
     , text_id
     , logtime
FROM   log log_1
WHERE  logtime =
(SELECT max(log_2.logtime)
   FROM log log_2
   WHERE  log_1.case_id = log_2.case_id
   AND    log_1.text_id = log_2.text_id
  where logtime <
 (select max(logtime) 
    from log log_3
   where log_3.case_id = log_2.case_id
     AND log_3.text_id = log_2.text_id))

 

[dnoeth]

As this is an ANSI forum ;-)

SELECT
case_id, text_id, log_time
FROM
(
SELECT
case_id, text_id, log_time,
ROW_NUMBER() OVER (PARTITION BY case_id, text_id
ORDER BY log_time DESC) AS r
FROM t
) dt
WHERE
r = 1 // one row
r <= 2 // two rows

Depending on how you want to deal with duplicates, you could use RANK instead of ROW_NUMBER.

Implemented by Teradata/Oracle/IBM...

Dieter