Funky SQL Server query that groups bunches of events

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hey!

I want to have a sql server query that groups similar events, but the 'grouping value' may occur multiple times, for example:

I have a database which stores where "a person" was seen. The database contains :

Hallway
Hallway
Kitchen
Kitchen
Kitchen
Livingroom
Kitchen
Livingroom
Livingroom
Hallway

I want te query to produce te following result :
2 Hallway
3 Kitchen
1 Livingroom
1 Kitchen
2 Livingroom
1 Hallway

Thus, group by the same location but only if they immidiately follow eachother up. Can anyone help me? Oh, and by the way... in the end I need a Linq query (on EF 6) that produces the result, so writing linq immidiately is no problem...

Posted 18-Mar-15 10:49am

Eduard Keilholz

Add a Solution

Comments

PIEBALDconsult 18-Mar-15 17:34pm

How do you know they'll come up in that order?

Eduard Keilholz 18-Mar-15 17:36pm

Because there's a datetime field of the event, I left it out because I thought it would be irrelevant for the result.

PIEBALDconsult 18-Mar-15 17:42pm

'Tisn't.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-03-18T11:52:00

Solution 1

Here is an example linq query: Row_Number() with Partition By clause simulation[^]
The idea on SQL query is the same ;)

Note: you need to set partition by date (day) when person seeen each other ;)

Posted 18-Mar-15 11:52am

Maciej Los

Updated 18-Mar-15 11:53am

v2

Abdulnazark · Answer 2 · 2015-03-20T03:06:00

SQL

declare @tb table(str1 varchar(50))
insert into @tb (str1) values
('Hallway'),
('Hallway'),
('Kitchen'),
('Kitchen'),
('Kitchen'),
('Livingroom'),
('Kitchen'),
('Livingroom'),
('Livingroom'),
('Hallway')
;with cte0(str1,rno)
as
(
select str1, rno=row_number() over(order by (SELECT 1)) from @tb
),
cte1 (str1,rno,pro) as
(
select str1,rno,pno=rno- row_number() over(partition by str1 order by cte0.rno,str1) from cte0
)

select count(*) as cnt, t.str1 from cte0 t inner join cte1 t1 on t.rno=t1.rno   group by t.str1,t1.pro order by min(t.rno)