Using max function

Question

I have two tables:

TotalTimeTable
(date date,time time)

FinalListA
(date date, time time, A int, B int)

I am running the following query:

SELECT t1.date,
       t1.time,
       max(t2.time)
FROM TotalTimeTable t1,
     FinalListA t2
WHERE t2.date=t1.date
  AND t2.time <= t1.time
GROUP BY t1.date,
         t1.time
ORDER BY t1.date,
         t1.time

Is there a way to pull out A and B from FinalListA where time = "max(t2.time)" in the same query?

TotalTimeTable:

date        time    
2006-01-01  9:30:01     
2006-01-01  9:30:02 
2006-01-01  9:30:03
2006-01-01  9:30:04
2006-01-01  9:30:05 
2006-01-01  9:30:06

FinalListA:

date        time     A B
2006-01-01  9:28:01  1 4
2006-01-01  9:30:02  2 3
2006-01-01  9:30:04  4 7
2006-01-01  9:30:07  6 4
2006-01-01  9:30:10  8 9
2006-01-01  9:30:11  1 2

The expected result is:

date        time     A B
2006-01-01  9:30:01  1 4
2006-01-01  9:30:02  2 3
2006-01-01  9:30:03  2 3
2006-01-01  9:30:04  4 7
2006-01-01  9:30:05  4 7
2006-01-01  9:30:06  4 7

The time is from TotalTimeTable FinalListA.time don't have to be in the result.

Please, provide sample rows.

Francesco De Lisi
– Francesco De Lisi

2013-04-17 09:54:56 +00:00
Commented Apr 17, 2013 at 9:54 — Francesco De Lisi
– Francesco De Lisi, Commented Apr 17, 2013 at 9:54

TechDo · Accepted Answer · 2013-04-17 11:36:58Z

1

Please try:

SELECT t1.date,
       t1.time,
       (select top 1 t2.A from FinalListA t2 where t2.date=t1.date and t2.Time<=t1.Time order by t2.Time desc) A,
       (select top 1 t2.B from FinalListA t2 where t2.date=t1.date and t2.Time<=t1.Time order by t2.Time desc) B
FROM TotalTimeTable t1

or using left join

select 
    x.date,
    x.Time,
    x.A,
    x.B
from(
    SELECT t1.*,
           t2.A,
           t2.B,
           ROW_NUMBER() over (PARTITION BY t1.date, t1.time order by t2.time desc) RNum
    FROM TotalTimeTable t1 left join
         FinalListA t2 on t1.date=t2.date and t2.Time<=t1.Time
)x where RNum=1

or using CROSS APPLY

SELECT t1.date,
       t1.time, 
       x.*
FROM TotalTimeTable t1 cross apply (
SELECT TOP 1 t2.A, t2.B from FinalListA t2 where t2.date=t1.date and t2.Time<=t1.Time order by t2.Time desc)x

edited Apr 17, 2013 at 11:36

answered Apr 17, 2013 at 9:54

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

TechDo Over a year ago

Please add sample data, and expected output.

Michael Cherevko Over a year ago

I've added sample data just now, i'll add expected output in a minute

TechDo Over a year ago

Please check the edited answer using sub-query and left join.

Michael Cherevko Over a year ago

Is there a way to do 2 top 1 select together? Besides, first query is the quickest and the results are correct.

TechDo Over a year ago

Please check the result using CROSS APPLY.

|

Serge · Accepted Answer · 2013-04-17 10:03:06Z

0

Using cross apply:

SELECT t1.date
   , t1.time
   , t2.time
   , t2.A
   , t2.B
FROM TotalTimeTable t1
CROSS APPLY (
 SELECT TOP(1)
  T2.time
 , T2.A
 , T2.B
FROM FinalListA t2
WHERE T2.date = T1.date
AND T2.time <= t1.time
ORDER BY T2.time DESC
) AS T2
ORDER BY t1.date,
     t1.time

answered Apr 17, 2013 at 10:03

Serge

6,7425 gold badges33 silver badges58 bronze badges

2 Comments

Michael Cherevko Over a year ago

Sorry, didn't had time to test it yet

Michael Cherevko Over a year ago

The result is correct but it is a little bit slower than of @techdo

Ackroydd · Accepted Answer · 2013-04-17 10:23:13Z

0

You can use your original query as a constraining subquery (without the ORDER BY)

SELECT t1.Date, t1.Time, t2.Time, t2.A, t2.B
FROM TotalTimeTable t1
INNER JOIN 
(
    SELECT t1.date,
           t1.time,
           maxt2Time = max(t2.time)
    FROM TotalTimeTable t1,
         FinalListA t2
    WHERE t2.date=t1.date
      AND t2.time <= t1.time
    GROUP BY t1.date,
             t1.time
) tmax
ON t1.Date = tmax.date AND t1.Time = tmax.Time
INNER JOIN FinalListA t2 ON t2.Date = tmax.Date AND t2.Time = tmax.maxt2Time

answered Apr 17, 2013 at 10:23

Ackroydd

1,4801 gold badge13 silver badges14 bronze badges

3 Comments

Ackroydd Over a year ago

@Serge's cross-apply below is about 50% faster

Michael Cherevko Over a year ago

Yes, this is how I'm planning to do it, but I want to check if there is simpler/faster query than this.

Ackroydd Over a year ago

Could reduce the the subquery to INNER JOIN (SELECT date, time = max(time) from FinalListA GROUP BY date) tmax

Collectives™ on Stack Overflow

Using max function

3 Answers 3

7 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related