Grouping problem
-
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
-
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
-
Many thanks for this - I'm now reading up about CTE's!!!
Regards
Tim
-
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) -
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] -
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) -
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] -
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.
-
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) -
. . . 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."
-
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 . . .:-DBWAA-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) -
Mark (6/12/2009)
See if this helpsDECLARE @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 -
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 -
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) -
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)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply