select help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select help

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2009-02-09 : 02:24:11

I have three columns namely, TIM, TCI, TCO respectively.
The table holds 24hr data starting from 00:15 am. The table looks like this : -
TIM TCI TCO
2009-02-01 00:15 2 5
...
2009-02-01 21:15 10 5
2009-02-01 21:30 20 10
2009-02-01 21:45 10 15
2009-02-01 22:00 10 10
...

Every 15minutes a row is generated. suppose the time input by the user is 2009-02-01 21:00
to 2009-02-01 22:00.
The query will do TCI + TCO within the above range and find out the max(TCI + TCO) along with TIM
that is, the result of the query will be just 30 2009-02-01 21:30.

I am thinking about creating temporary table, but maybe one query will give me the desired result much more faster.
thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 03:09:23

[code]DECLARE @Sample TABLE
(
TIM DATETIME,
TCI INT,
TCO INT
)

INSERT @Sample
SELECT '2009-02-01 00:15', 2, 5 UNION ALL
SELECT '2009-02-01 21:15', 10, 5 UNION ALL
SELECT '2009-02-01 21:30', 20, 10 UNION ALL
SELECT '2009-02-01 21:45', 10, 15 UNION ALL
SELECT '2009-02-01 22:00', 10, 10

DECLARE @From DATETIME,
@To DATETIME

SELECT @From = '20090201 21:00',
@To = '20090201 22:00'

SELECT TOP 1 *
FROM @Sample
WHERE TIM >= @From
AND TIM < @To
ORDER BY TCI + TCO DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 03:32:17

[code]SELECT TIM
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY TCI + TCO DESC) AS Seq,
TIM
FROM table
)t
WHERE Seq=1
[/code]

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2009-02-09 : 03:43:10

thank you for the response peso.
i didn't know that i could use something + something in ORDER BY clause.
coz i am not that good in sql you know.. :)

I am using Oracle 9i.
but here i just described the situation.
and you showed me the way, the concept is same basically.

i used the following query and it works perfectly.

select tci, tco, (tci + tco)
from (
select tci, tco, (tci + tco) ,
rank() over (order by (tci + tco) desc) sal_rank
from tgrp
where time >= '2009-02-01 21:15'
and time <= '2009-02-01 22:14')
where sal_rank<=1;

thank you so much

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2009-02-09 : 03:45:33

oh ...

i didn't notice visakh16 has posted another reply.
thank you sir.
i think i did something similar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:04:42

quote:
Originally posted by cyberpd

oh ...

i didn't notice visakh16 has posted another reply.
thank you sir.
i think i did something similar.

you're welcome
using rank will return you all the rows having the highest sum, not sure if thats what your reqmnt is. if you want just one row use row_number() instead

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2009-02-09 : 04:15:40

yes i wanted highest tci + tco and the time when this happened.
thanks for the reply, i have noted the query you wrote.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 04:20:50

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources