Solved

how to query database from this data. ( see inside )

Posted on 2014-07-22
6
367 Views
Last Modified: 2014-07-30
Hello, Expert

I got the data and schema like this

[id] --- [name] --- [timestamp] - [ type]
 1    ---    A   ---- 2012-09-01 10:00:00 --- 1
 2    ---    A   ---- 2012-09-01 10:01:00 --- 2
 3    ---    B   ---- 2012-09-01 10:01:00 --- 1
 4    ---    A   ---- 2012-09-01 10:02:00 --- 2
 5    ---    C   ---- 2012-09-01 10:01:20 --- 1
 6    ---    A   ---- 2012-09-01 10:03:00 --- 2
 7    ---    A   ---- 2012-09-01 10:03:10 --- 2
 8    ---    A   ---- 2012-09-01 10:03:00 --- 1
 9    ---    A   ---- 2012-09-01 10:03:30 --- 1
 10    ---    A   ---- 2012-09-01 10:00:20 --- 1
from above, i want to select "A" with condition
 - type 1 come first  and must follow by 2 then follow by 1 again ( with time sort )....
the result i expected is

A   ---- 2012-09-01 10:00:20 --- 1
A   ---- 2012-09-01 10:03:10 --- 2
A   ---- 2012-09-01 10:03:30 --- 1

is it possible to do this in database side. ( any database sql , nosql  , mssql , etc.. )

Thanks,
0
Comment
Question by:sora-x
6 Comments
 
LVL 6

Expert Comment

by:rjohnsonjr
ID: 40212289
select name,timestamp, type

from table
where
name='A'
order by timestamp,type asc
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40212295
select [name],[timestamp], [type] from table where [name]='A' order by [timestamp]

Open in new window

0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 40213649
Can't really see the logic in the output:

  - type 1 come first  and must follow by 2 then follow by 1 again ( with time sort )....

I interpret it something like:
- sort type 1 entries by time
- sort type 2 entries by time
- comb the two together

but your output goes something like:
- pick oldest type 1 entry
- pick newest type 2 entry
- pick type 1 entry that is greater than prev type 2 entry

Can you please provide a sort of the full input set, or describe the sorting requirements precisely?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sora-x
ID: 40213976
sorry for my poor expected output it's should be

A   ---- 2012-09-01 10:00:00 --- 1
A   ---- 2012-09-01 10:01:00 --- 2
A   ---- 2012-09-01 10:03:00 --- 1
A   ---- 2012-09-01 10:03:10 --- 2
A   ---- 2012-09-01 10:03:30 --- 1

the sorting requirement is

- 1 always come before 2
- display the oldest 1
- after display 1 the next must be 2 if got many 2 in the time range display the oldest. ( type 2 timestamp greater than prev
 type 1  but less than next type 1 (if have )   )

- then display next type 1 timestamp that greater than type 2 but less than next type 2 ( if have )
- repeat ..

Thanks
0
 

Author Closing Comment

by:sora-x
ID: 40228753
This comment inspired me how to do it. Thanks
0
 
LVL 8

Expert Comment

by:Surrano
ID: 40228856
Glad I could be your muse ;)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question