Prev: Transactional Replication and Backups
Next: Converting SQL Server 2005 to SQL Express 2008
From: Bhavin on 26 Mar 2010 08:49
Hi All,

I am working on a table (mentioned below) I am looking for a query
which can get me the data according to the “id” column with respect to
speed.
The condition is that I have to get three consecutive entries which
have speed > 60
Below is the sample table with data on which I have to retrieve the
data on above condition.

The output i need can be as given below

DVXC002 12/10/09 0:12 96
DVXC002 12/10/09 18:40 89
DVXC002 12/10/09 19:43 65

DVXC005 12/10/09 11:56 69
DVXC005 12/10/09 15:26 62
DVXC005 12/10/09 17:35 85

Need your help urgently....Thanks in advance.

Sample table with Data:

id received_date lattitude longitude speed
====================================================
DVXC001 12/3/09 2:57 2309.01684 7247.4252 10
DVXC003 12/5/09 19:12 2336.4392 7222.9008 3
DVXC002 12/9/09 14:23 2314.05834 7238.0444 38
DVXC001 12/9/09 12:43 2309.01928 7247.436 75
DVXC001 12/9/09 18:43 2312.66967 7231.7836 53
DVXC002 12/10/09 0:12 2312.66528 7231.7797 96
DVXC005 12/10/09 3:30 2312.66772 7231.7817 5
DVXC005 12/10/09 11:56 2312.96313 7238.3833 69
DVXC005 12/10/09 15:26 2314.26293 7234.3208 62
DVXC003 12/10/09 15:55 2312.6643 7231.7817 6
DVXC005 12/10/09 17:35 2312.66918 7231.7817 85
DVXC004 12/10/09 18:35 2313.57006 7243.8627 10
DVXC002 12/10/09 18:40 2312.66528 7231.7797 89
DVXC004 12/10/09 18:48 2313.57006 7243.8627 20
DVXC003 12/10/09 19:15 2312.6643 7231.7817 0
DVXC002 12/10/09 19:43 2312.66528 7231.7797 65
DVXC004 12/10/09 19:55 2313.57006 7243.8627 0
From: Plamen Ratchev on 26 Mar 2010 12:46
Here is one solution (I think it can be simplified, but do not have time to look further):

SELECT id, received_date, speed
FROM (
SELECT id, received_date, speed, rk2,
COUNT(*) OVER(PARTITION BY id, grp) AS cnt
FROM (
SELECT id, received_date, speed, rk1,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY rk2) AS rk2,
rk1 - ROW_NUMBER() OVER(PARTITION BY id ORDER BY rk2) AS grp
FROM (
SELECT id, received_date, speed,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY received_date) AS rk1,
CASE WHEN speed > 60 THEN
ROW_NUMBER() OVER(PARTITION BY id ORDER BY received_date)
END AS rk2
FROM Foo) AS F
WHERE rk2 IS NOT NULL) AS T) AS X
WHERE cnt > 2
AND rk2 <= 3;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Mark Fitzgerald on 26 Mar 2010 12:53
Initial Query:

select * from Speeds S1
where Speed > 60
and S1.ID IN
(select ID
from Speeds
where Speed > 60
group by ID
having COUNT(*) >= 3)
order by ID,Speed desc

1) Any ID with under 3 counts of speeds over 60 will be ignored
2) Assuming you wants the order from fastest to slowest

Pivotted Version to show columns for 1st 2nd and 3rd:

select *
from
(select ID,Speed, RANK() over (partition by ID order by speed desc) as
SpeedPosition
from Speeds S1
where Speed > 60
and S1.ID IN
(select ID
from Speeds
where Speed > 60
group by ID
having COUNT(*) >= 3)) as A
pivot
(max(speed) for SpeedPosition IN ([1],[2],[3])) as D

Fitz

"Bhavin" <bhavin.vyas(a)gmail.com> wrote in message
news:af7e0ce2-8cc7-44e3-a1f7-ebdd0c49ae4b(a)m25g2000prj.googlegroups.com...
> Hi All,
>
> I am working on a table (mentioned below) I am looking for a query
> which can get me the data according to the �id� column with respect to
> speed.
> The condition is that I have to get three consecutive entries which
> have speed > 60
> Below is the sample table with data on which I have to retrieve the
> data on above condition.
>
> The output i need can be as given below
>
> DVXC002 12/10/09 0:12 96
> DVXC002 12/10/09 18:40 89
> DVXC002 12/10/09 19:43 65
>
> DVXC005 12/10/09 11:56 69
> DVXC005 12/10/09 15:26 62
> DVXC005 12/10/09 17:35 85
>
> Need your help urgently....Thanks in advance.
>
> Sample table with Data:
>
> id received_date lattitude longitude speed
> ====================================================
> DVXC001 12/3/09 2:57 2309.01684 7247.4252 10
> DVXC003 12/5/09 19:12 2336.4392 7222.9008 3
> DVXC002 12/9/09 14:23 2314.05834 7238.0444 38
> DVXC001 12/9/09 12:43 2309.01928 7247.436 75
> DVXC001 12/9/09 18:43 2312.66967 7231.7836 53
> DVXC002 12/10/09 0:12 2312.66528 7231.7797 96
> DVXC005 12/10/09 3:30 2312.66772 7231.7817 5
> DVXC005 12/10/09 11:56 2312.96313 7238.3833 69
> DVXC005 12/10/09 15:26 2314.26293 7234.3208 62
> DVXC003 12/10/09 15:55 2312.6643 7231.7817 6
> DVXC005 12/10/09 17:35 2312.66918 7231.7817 85
> DVXC004 12/10/09 18:35 2313.57006 7243.8627 10
> DVXC002 12/10/09 18:40 2312.66528 7231.7797 89
> DVXC004 12/10/09 18:48 2313.57006 7243.8627 20
> DVXC003 12/10/09 19:15 2312.6643 7231.7817 0
> DVXC002 12/10/09 19:43 2312.66528 7231.7797 65
> DVXC004 12/10/09 19:55 2313.57006 7243.8627 0

From: Plamen Ratchev on 26 Mar 2010 13:16
Mark,

This will not return three "consecutive" entries which have speed > 60, as requested. It will return any 3.

--
Plamen Ratchev
http://www.SQLStudio.com
From: Bhavin on 27 Mar 2010 07:57
On Mar 26, 10:16 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> Mark,
>
> This will not return three "consecutive" entries which have speed > 60, as requested. It will return any 3.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Thats right Plamen,

Let me see if i can find the solution other way round. Your given
query helped though.

-Bhavin
 |  Next  |  Last
Pages: 1 2
Prev: Transactional Replication and Backups
Next: Converting SQL Server 2005 to SQL Express 2008