Post reply

Grouping problem

  • 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

  • June 12, 2009 at 8:36 am

    #1008707

    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

  • June 12, 2009 at 9:06 am

    #1008733

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

    Regards

    Tim

  • June 12, 2009 at 10:59 pm

    #1009006

    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)
    Intro to Tally Tables and Functions

  • June 14, 2009 at 2:33 am

    #1009268

    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]

  • June 14, 2009 at 10:54 am

    #1009328

    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)
    Intro to Tally Tables and Functions

  • June 14, 2009 at 11:56 am

    #1009346

    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]

  • June 14, 2009 at 1:40 pm

    #1009385

    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.

  • June 14, 2009 at 3:53 pm

    #1009402

    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)
    Intro to Tally Tables and Functions

  • June 18, 2009 at 10:23 am

    #1011915

    . . . 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."

  • June 20, 2009 at 6:25 pm

    #1012912

    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)
    Intro to Tally Tables and Functions

  • June 20, 2009 at 10:27 pm

    #1012953

    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

  • June 20, 2009 at 10:29 pm

    #1012954

    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

  • June 21, 2009 at 12:41 pm

    #1013059

    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)
    Intro to Tally Tables and Functions

  • June 21, 2009 at 12:42 pm

    #1013060

    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)
    Intro to Tally Tables and Functions

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

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