Retrieving sorted data from same table.

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 =93id=94 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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
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
0
Bhavin
3/26/2010 12:49:21 PM
sqlserver.server 1327 articles. 0 followers. Follow

5 Replies
523 Views

Similar Articles

[PageSpeed] 48

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
0
Plamen
3/26/2010 4:46:28 PM
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@gmail.com> wrote in message 
news:af7e0ce2-8cc7-44e3-a1f7-ebdd0c49ae4b@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 

0
Mark
3/26/2010 4:53:01 PM
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
0
Plamen
3/26/2010 5:16:43 PM
On Mar 26, 10:16=A0pm, Plamen Ratchev <Pla...@SQLStudio.com> wrote:
> Mark,
>
> This will not return three "consecutive" entries which have speed > 60, a=
s 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
0
Bhavin
3/27/2010 11:57:44 AM
The query that I posted works fine for consecutive entries, but you may look for ways to simplify it.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/27/2010 2:24:51 PM
Reply:

Similar Artilces:

Advanced find
In advance find a customer wishes to know how many of each product an account has ordered. The Order lines, Quote Lines, Invoice Lines are not available as output entities. Therefore no reporting on advanced find is avaiable. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message...

merging into Word with filtered data
Hello, I'm trying to do a merge in Word using data from Excel. However, I don't want to merge using data from the whole worksheet, but just filtered data (all people from London - place is one of the fields in the worksheet. I have no idea how I might do this. Any help gratefully received. I believe you can accomplish this from Word by using "Query Options" and selecting to merge only those records you're seeking. HTH PC "Italian Pete" <ItalianPete@discussions.microsoft.com> wrote in message news:3E62FCDC-42EC-48DB-92AC-857B224F085F@microsoft.c...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

How to combine two identically structured tables to query them
Hi, I am sure there is a way that you can have a query that effectively takes two separate tables that have the identical column layout and add them together and then perofrm a query on the combined set of data. I just can't remember how to do it. My reason for need in this is that I have a table called "SellerNumbers" that has 4 million rows of data in it and what I want to do to improve performance is archive the oldest 2 years worth of data to an identical table called "SellerNumbersArchive", however the users want to be able to occasionaly run quer...

Data Loss in Publisher
Hi I run a W2k network in a school. I have Office Group Policies in place forcing the default save location for Publisher documents (Office XP version) to the pupil's home folders. All my PCs are identical (ghost images). Pupils have roaming profiles (some mandatory). Some users save work in Publisher and the next time they log on to the network and open the saved file, it's contents have gone! There is nothing exceptional about the affected users' user accounts and they have a mixture of mandatory and roaming profiles. Has anyone come across this problem before? Than...

Any way to clear out stock pricing data before or after going to Quicken?
With all the discussion a few months ago about taking control of our own data, I had gone through the steps to look at my Money file as an Access DB, and found that of my 80MB, more than half was daily stock prices. Thats not something I really want to convert and maintain. I can't imagine that its really needed, since transactions have their own price info. Is there any way to get rid of it in Money, in Quicken, or other? Thanks In Money Plus: Portfolio > Other Tasks > More > Clear historical quotes The process may be different in earlier versions. "JDelMar&q...

Pivot table #11
I asked this yesterday but I am having difficulty getting to my original post. Page 1 of this news group keeps brining me to feb 17th.... anyways this is what i asked yesterday: Is there a way I can set up a pivot table with sub categories? i.e. Type of Contact is my heading and under it I have 4 categories (phone, walk-in, etc...) Thanks. A. I got this reply: If you add Type of Contact to the row area, you should see all of the categories. Can you describe the fields in your data table, and how you'd like them to appear in the pivot table? My answer to this is: I would like...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

matrix and sorting with running value
I have a matrix which has 1 row grouping and 1 column grouping. The column is a date. The matrix has 1 field which is doing a running sum. I want the date column sorted in descedning order. I changed the sort of the column group and the dates do appear to sort in desc order. However the runningsum still appears to sort as if the date column is in asc order. ...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

Connecting tables
Is there any way to connect tables in Publisher similiar to the way you can connect text frames for overfill. kpappano wrote: > Is there any way to connect tables in Publisher similiar to the way you can > connect text frames for overfill. Nope. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Is there any other way I can do it? Any suggestions? "Ed Bennett" wrote: > kpappano wrote: > > Is there any way to connect tables in Publisher similiar to the way you can > > connect text frames for overfill. > > Nope. > > -- > E...

summarise data
I have data on a sheet looking like this 5/5/2004 5/5/2004 7/5/2004 7/5/2004 7/5/2004 7/5/2004 8/5/2004 9/5/2004 9/5/2004 9/5/2004 and I want to summarise this into different cells so that it look like 5/5/2004 7/5/2004 8/5/2004 9/5/2004 i.e. I want to list individual occurances of repeating dates o numbers. Any help would be much appreciated -- Message posted from http://www.ExcelForum.com Sounds like a job or a Pivot Table perhaps? http://www.ozgrid.com/Excel/excel-pivot-tables.htm ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore...

creating a program that uses data from a sensor....
Hello, I want to create an MFC program that uses parameter values passed to it from a meter (specifically the resistance in ohms of resisters in an electronic circuit). What are the hardware components that would be needed? And what MFC class(es) would I use to accomplish this? Thanks, RABMissouri2006 On 21 Oct 2006 08:10:50 -0700, "RAB" <rabmissouri@yahoo.com> wrote: >Hello, > >I want to create an MFC program that uses parameter values passed to it >from a meter (specifically the resistance in ohms of resisters in an >electronic circuit). What are the ha...

Data archive with GP Manufacturing
Has anyone had experience with any data archive with GP Manufacturing. The product from Professional Advantage does not support Manufacturing archive, at least not in the documentation. Don't have an answer for you but we will be doing the same in the next week or so. Will report back when done. "DavidInterDyn" wrote: > Has anyone had experience with any data archive with GP Manufacturing. The > product from Professional Advantage does not support Manufacturing archive, > at least not in the documentation. ...

Transfering Outlook Data to a New PC
I've just built a new PC. Currently it has nothing on it other than Windows. When I install Outlook 2003 I'll need to transfer the data from another PC onto it, including all folders, emails and attachments in them and calendar data. What's the easiest and most effective way to do this? Find the PST file and copy it to the new machine and into the appropriate directory, what? Thanks. Copy the pst(s) to the new PC There isnt an appropriate Folder, there is a default folder created when Outlook is installed. http://www.howto-outlook.com/howto/backupandrestore.htm "Marts&q...

Rich Text & Sorting
I have a database that I need alphabetical and I want to color code some of the names. I have sorted the database alpahbetically and saved it with a new name. To sort it, I have to use Text format in my first and last name fields. When I have them listed as Rich Text it will not give me the option to sort when I highlight either of those fields. When I go in and change the name fields to Rich Text so that I can color code them, my database reverts back to not being alphabetical. What am I missing or doing wrong? Being able to color code fields is wonderful, so glad they final...

Locking of entered data
I haev a form that currently allows you to enter new records and edit existing records. My key field is based on a number entered by the user from another system. Once the number is entered I do not want the user to be able to type over that number but still be aloud to add a new record. I tried the AllowAdditions and AllowEdits in the properties but if I set them to Yes AllowAdditions and No AllowEdits then nothing can be changed. I want them to be able to edit everything but the one field containing the number. Ideas? Glenna, The code I provided would go in the two places that I...

how can I split one cell's data into two cells?
I am using Excel as a database, and have a column with cells with a person's name (text) in it...e.g., John Smith. NOW, after the fact, I wish to have a column with just John in it, and another column with just Smith...but I have NO idea how to do this! Help would be appreciated. Thank you. Please refrain from multiposting, you have answers in one of the 4! newsgroups you posted the same question in. Multiposting is frowned upon and even the MS web interface states so -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It ...

Losing data in flashdrive
I am experiencing in losing data in flashdrives. It seems that when files are copied onto flashdrive, I should wait for a while before removing flashdrives from USB port. Does anybody know how long should I wait before unplugging flashdrives. Jorge Are you using the Safely Remove Hardware icon in the notification area? If so, it should tell you when you can remove the drive. ....Alan -- Alan Edwards, MS MVP Windows - Internet Explorer http://dts-l.com/index.htm On Mon, 1 Mar 2010 17:29:25 -0700, in microsoft.public.windowsxp.general, "Jorge Cervantes"...

What table....
What table does the "More addresses" section use? The "More addresses" is found on the side when you are filling out a contact. CustomerAddress Base Dave "GDaxon" <GDaxon@discussions.microsoft.com> wrote in message news:BA8C768F-3664-4FAF-9CBA-6F6F28C8FD64@microsoft.com... > What table does the "More addresses" section use? The "More addresses" is > found on the side when you are filling out a contact. Dear, Check Customeraddressbase. I hope this will help u. Thanks, MVP- CRM/.Net mscrmexpert@gmail.com "GDaxon&qu...

Size of Data input mask
How can I change the size of an Excel Input Mask? Do you mean you want to limit (or even check) the number of characters in a cell when the user is entering data? If yes, take a look at Data|Validation|Settings tab|Allow Text Length If no, then you may want to clarify your question more. Juerg Rohner wrote: > > How can I change the size of an Excel Input Mask? -- Dave Peterson ec35720@msn.com ...

Look up table?
I am sure this is simple, but I dont know how to achieve it I have 2 drop down lists the produce values 1-3 each and a table A B C 1 500 550 600 2 450 500 550 3 400 450 500 How do I get the value at the intersection of the row/coloum, so if the drop downs come up with 1 & b the answer should be $550 Thanks >I have 2 drop down lists the produce values 1-3 each >if the drop downs come up with 1 & b If both drop downs have selections of 1,2 3 then how can the drop down...

Publisher Data Base
Is there any way to convert an existing excel address list to a Publisher data base for mail merge? Since there are close to 1000 entries I do not want to manually add each one. You don't have to convert anything. Use the Excel list for the mail merge. -- JoAnn Paules MVP Microsoft [Publisher] "Richard Las Vegas" <Richard Las Vegas@discussions.microsoft.com> wrote in message news:909EDBF8-26DC-4661-B36C-1D3FE52CB66E@microsoft.com... > Is there any way to convert an existing excel address list to a Publisher > data base for mail merge? Since there are close t...

Need to sort dates before 1900 in proper order
I need to sort dates before 1900 in proper order -- ones after 1900 are all fine but any dates like 03/29/1865 will always sort by month and day but NEVER the year except in the month area. Don't know if I am making sense -- but will be like: 03/29/1865 03/29/1866 03/29/1873 04/01/1863 04/01/1868 etc. Thanks, Judy M. I can provide a chunky workaround, until someone provides a better answer: Assuming your 5 sample dates are in cells A1 ~ A5, and that single digit months and days are always expressed with a leading zero: In cell B1, enter this formula: =VALUE(MID(A1,1,2)) In C1, ente...

Split Database, Can't Append to Table
I have a table to which I daily upload financial reports into one table, and run an append query to add those records not already listed in the master table. I recently split the database because of some other random conflicts we began to have once two users began to access the database. I read that splitting might help. Now I am unable to append to the master table using the append query. I get a message that there is a violoation regarding an index, primary key, or relationship. I've also tried to manually cut & paste the records from the results of the append query with n...