Grouping problem

Question

Post reply

Grouping problem

Tim Hobbs

SSC Enthusiast

Points: 163
More actions
June 12, 2009 at 8:18 am

#222460

I can't even work out how to describe what I'm trying to do, so googling isn't going too well!
My scenario is a vehicle scheduled to work on Street A (5 jobs), then Street B (2 jobs) and then Street A again (3 jobs). I have individual records for each job with a sequence field to put them in order.
If I do a straighforward GROUP BY to get a summary it shows (as expected)
Street A 8 jobs
Street B 2 jobs
What I need is a way to get it to output
Street A 5 jobs
Street B 2 jobs
Street A 3 jobs
How can I query for this?
Many thanks
Tim

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

See if this helps

DECLARE @Jobs TABLE(Street VARCHAR(10), Seq INT)

INSERT INTO @Jobs(Street, Seq)

SELECT 'Street A',1 UNION ALL

SELECT 'Street A',2 UNION ALL

SELECT 'Street A',3 UNION ALL

SELECT 'Street A',4 UNION ALL

SELECT 'Street A',5 UNION ALL

SELECT 'Street B',6 UNION ALL

SELECT 'Street B',7 UNION ALL

SELECT 'Street A',8 UNION ALL

SELECT 'Street A',9 UNION ALL

SELECT 'Street A',10;

SELECT Street,

COUNT(*) AS Jobs

FROM @Jobs

GROUP BY Street;

WITH CTE AS (

SELECT Street,Seq,

ROW_NUMBER() OVER(ORDER BY Seq) -

ROW_NUMBER() OVER(PARTITION BY Street ORDER BY Seq) AS rnDiff

FROM @Jobs)

SELECT Street,

COUNT(*) AS Jobs

FROM CTE

GROUP BY Street,rnDiff

ORDER BY MIN(Seq)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Tim Hobbs SSC Enthusiast Points: 163 More actions · Answer 2

Many thanks for this - I'm now reading up about CTE's!!!

Regards

Tim

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Tim Hobbs (6/12/2009)
Many thanks for this - I'm now reading up about CTE's!!!
Regards
Tim

It's not the CTE that did the magic. It's the way one ROW_NUMBER was compared to another. Play with that first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

I guess almost everybody knows this trick by now?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

RBarryYoung (6/14/2009)
I guess almost everybody knows this trick by now?

Heh... apparently not. The OP didn't and neither did the 30 or so DBA's/Developers that I gave a couple of training sessions to last Thursday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Jeff Moden (6/14/2009)
RBarryYoung (6/14/2009)
I guess almost everybody knows this trick by now?
Heh... apparently not. The OP didn't and neither did the 30 or so DBA's/Developers that I gave a couple of training sessions to last Thursday.

Good Job. 🙂

Maybe I should have said: "I guess everyone answering SQL questions on the internet seems to know this trick by now." Oh well, ...:cool:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Tim Hobbs SSC Enthusiast Points: 163 More actions · Answer 7

Mark's original answer sorted my problem in no time at all; so thanks for that. I'm familiar with ROW_NUMBER but hadn't thought of using it in this context.

As you say, the CTE bit isn't really the key but it's something I wasn't even aware existed.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Tim Hobbs (6/14/2009)
Mark's original answer sorted my problem in no time at all; so thanks for that. I'm familiar with ROW_NUMBER but hadn't thought of using it in this context.
As you say, the CTE bit isn't really the key but it's something I wasn't even aware existed.

Very cool. Thanks for the feedback, Tim.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

. . . and then there are the unfortunate few who keep seeing you lob out these answers, but are still anxiously awaiting 2005 servers. Luckily everyone seems to be asking all the questions I'll need answered when we get there, I'll pretend that's an advantage of being behind the curve . . .:-D

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

jcrawf02 (6/18/2009)
. . . and then there are the unfortunate few who keep seeing you lob out these answers, but are still anxiously awaiting 2005 servers. Luckily everyone seems to be asking all the questions I'll need answered when we get there, I'll pretend that's an advantage of being behind the curve . . .:-D

BWAA-HAAA!!!! I felt exactly the same way waiting for the folks at work to do an upgrade (they're still not there). I finally broke down in Dec 2007 and bought a copy of the Developer's Edition just so I could try to keep up with the rest of the world.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 11

Mark (6/12/2009)
See if this helps
DECLARE @Jobs TABLE(Street VARCHAR(10), Seq INT)
INSERT INTO @Jobs(Street, Seq)
SELECT 'Street A',1 UNION ALL
SELECT 'Street A',2 UNION ALL
SELECT 'Street A',3 UNION ALL
SELECT 'Street A',4 UNION ALL
SELECT 'Street A',5 UNION ALL
SELECT 'Street B',6 UNION ALL
SELECT 'Street B',7 UNION ALL
SELECT 'Street A',8 UNION ALL
SELECT 'Street A',9 UNION ALL
SELECT 'Street A',10;
SELECT Street,
COUNT(*) AS Jobs
FROM @Jobs
GROUP BY Street;
WITH CTE AS (
SELECT Street,Seq,
ROW_NUMBER() OVER(ORDER BY Seq) -
ROW_NUMBER() OVER(PARTITION BY Street ORDER BY Seq) AS rnDiff
FROM @Jobs)
SELECT Street,
COUNT(*) AS Jobs
FROM CTE
GROUP BY Street,rnDiff
ORDER BY MIN(Seq)

Hi Mark,

I have a question about the above code.

You used: ROW_NUMBER() OVER(ORDER BY Seq)It is producing the same thing as the Seq column by itself. Is there a reason why you went through the trouble to use the code you did instead of the column?

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 12

Jeff Moden (6/20/2009)
BWAA-HAAA!!!! I felt exactly the same way waiting for the folks at work to do an upgrade (they're still not there). I finally broke down in Dec 2007 and bought a copy of the Developer's Edition just so I could try to keep up with the rest of the world.

And have you since bought the 2008 Dev Edition? 😉

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

WayneS (6/20/2009)
Jeff Moden (6/20/2009)
BWAA-HAAA!!!! I felt exactly the same way waiting for the folks at work to do an upgrade (they're still not there). I finally broke down in Dec 2007 and bought a copy of the Developer's Edition just so I could try to keep up with the rest of the world.
And have you since bought the 2008 Dev Edition? 😉

Nope... not yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

WayneS (6/20/2009)
You used: ROW_NUMBER() OVER(ORDER BY Seq)It is producing the same thing as the Seq column by itself. Is there a reason why you went through the trouble to use the code you did instead of the column?

In this case it is. There's no guarantee that no deletes will have been done on the source table in real life. Better to be safe by including the ROW_NUMBER to guarantee the proper operation.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)