Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Running group by on column

    I want to do sort of a group by, sort, joining of data.

    A record in the table is telling something about the next records

    I'm not really sure how to describe the thing query I want to make. If someone has a good description for what I like to do, the topictitle can be changed.

    I will give an example of the important parts of the table and some test records.

    Records
    Code:
    1	10	Start Unit 1
    1	20	Add
    1	30	Add
    1	40	Mix
    1	50	End Unit 1
    1	60	Start Unit 2
    1	70	Add
    1	80	End Unit 2
    1	90	Start Unit 1
    1	100	Add
    1	100	End Unit 1
    2	10	Start Unit 2
    2	20	Add
    2	30	End Unit 2
    2	40	Start Unit 2
    2	50	Add
    2	60	End Unit 2
    2	70	Start Unit 1
    2	80	Add
    2	90	End Unit 1
    Expected Results
    Code:
    1	20	Add	Unit 1
    1	30	Add	Unit 1
    1	40	Mix	Unit 1
    1	70	Add	Unit 2
    1	100	Add	Unit 1
    2	20	Add	Unit 2
    2	50	Add	Unit 2
    2	80	Add	Unit 1
    As you can see in the above, Start and Stop identifiers tell something about records in between. Is there a good way to do this with a query? I tried various options, but couldn't get to a good result. Help is much appreciated.

    Code:
    CREATE TABLE [dbo].[formulasteps](
    	[formula] [int] NULL,
    	[stepno] [int] NULL,
    	[description] [nvarchar](50) NULL
    )
    Code:
    INSERT INTO formulasteps VALUES (1,10,'Start Unit 1');
    INSERT INTO formulasteps VALUES (1,20,'Add');
    INSERT INTO formulasteps VALUES (1,30,'Add');
    INSERT INTO formulasteps VALUES (1,40,'Mix');
    INSERT INTO formulasteps VALUES (1,50,'End Unit 1');
    INSERT INTO formulasteps VALUES (1,60,'Start Unit 2');
    INSERT INTO formulasteps VALUES (1,70,'Add');
    INSERT INTO formulasteps VALUES (1,80,'End Unit 2');
    INSERT INTO formulasteps VALUES (1,90,'Start Unit 1');
    INSERT INTO formulasteps VALUES (1,100,'Add');
    INSERT INTO formulasteps VALUES (1,100,'End Unit 1');
    INSERT INTO formulasteps VALUES (2,10,'Start Unit 2');
    INSERT INTO formulasteps VALUES (2,20,'Add');
    INSERT INTO formulasteps VALUES (2,30,'End Unit 2');
    INSERT INTO formulasteps VALUES (2,40,'Start Unit 2');
    INSERT INTO formulasteps VALUES (2,50,'Add');
    INSERT INTO formulasteps VALUES (2,60,'End Unit 2');
    INSERT INTO formulasteps VALUES (2,70,'Start Unit 1');
    INSERT INTO formulasteps VALUES (2,80,'Add');
    INSERT INTO formulasteps VALUES (2,90,'End Unit 1');

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    I got this (it's not pretty):
    Code:
    select fs1.*, 
           (select top 1 substring(description, 7, 44) as Unit
              from formulasteps fs2
             where description like 'Start%' 
                and fs1.formula = fs2.formula
                and fs1.stepno > fs2.stepno
              order by stepno desc) 
      from formulasteps fs1
     where fs1.description not like 'Start%'
       and fs1.description not like 'End%'
    If you understand it you may use it
    Last edited by ivon; 05-04-09 at 11:15.

  3. #3
    Join Date
    May 2009
    Posts
    5
    Thanks...looks very promising.

    I will try to modify it to my requirements.

    Do you have any idea about performance? Something to worry about or not?

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    I have no idea about the performance, sorry. The '>' worries me, though :-)

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    This basically returns the same results:

    select fs1.*
    from formulasteps fs1
    where fs1.description not like 'Start%'
    and fs1.description not like 'End%'
    order by formula, stepno

    Both mine and ivon's queries will do table scans (mine has 1 and ivon's has 2)as there aren't any indexes. For ten rows, not much impact but for millions of rows, you may want to add an index or two. I added one on description and it now does an index scan as opposed to a table scan. I also added one on formula, stepno and I no longer have a sort in my execution plan.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, Ivon's will have more than 2 scans. Without indexes, he will have N + 1 scans (where N is the number of rows in the table).

    However, although more efficient yours dun't get the answer
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    How so? I ran the OP's create and insert and then my query and it returned the OP's desired results. What did I miss?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    His result set is four columns, your is three. The tricky part is getting the Unit Number, which is all the difference between Ivon's and yours.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •