Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Tough Problem...

    I need a query that will return data in the following format.

    Col1 Col2 Count
    ---------------
    A | B | 1
    A | C | 2
    A | D | 3
    A | E | 4
    B | A | 1
    B | B | 2
    B | C | 3
    --------------

    In other words I want to group the results by col1 and when col1 one changes I want to restart my rowcount. I also want to return the row count for each record.

    I have tried many different methods, but I am starting to think that this is not even possible.

    Any ideas?

    Thanks...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This would be best handled by the client. If you are running any kind of reporting software (such as Crystal Reports, MS-Access, etc) this is easy to do on the client.

    If you must do it on the server, you have two choices. If there is a unique column or columns, you can use it to derive the count which can give you the kind of numbering that you desire. If not, you'll need to create a temp table and add the count informtion there.

    Be clear on this though, this task is better handled by the client than by the server!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat,

    Sorry, but I'll respectfully disagree with the "client-based" approach:

    set nocount on
    go
    create table test (col1 char(1) not null, col2 char(1) not null)
    go
    insert test values ('A', 'A')
    insert test values ('A', 'B')
    insert test values ('A', 'C')
    insert test values ('A', 'D')
    insert test values ('A', 'E')
    insert test values ('B', 'A')
    insert test values ('B', 'B')
    insert test values ('B', 'C')
    insert test values ('B', 'D')
    insert test values ('B', 'E')
    go
    select c1.col1, c1.col2, count(*) from test c1
    inner join test c2
    on c1.col1=c2.col1 and c1.col2>=c2.col2
    group by c1.col1, c1.col2
    order by c1.col1, c1.col2
    go
    drop table test
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry Pat: NOTHING IS EASIER IN CRYSTAL!!!!!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Sorry, but I'll respectfully disagree with the "client-based" approach:
    Ok, then to borrow your example (and ignore the caveat I threw into my previous posting), try:
    Code:
    set nocount on
    go
    create table test (col1 char(1) not null, col2 char(1) not null)
    go
    insert test values ('A', 'A')
    insert test values ('A', 'A')
    insert test values ('A', 'A')
    insert test values ('A', 'A')
    insert test values ('A', 'A')
    insert test values ('B', 'A')
    insert test values ('B', 'A')
    insert test values ('B', 'A')
    insert test values ('B', 'A')
    insert test values ('B', 'A')
    go
    select c1.col1, c1.col2, count(*) from test c1 
    inner join test c2
    on c1.col1=c2.col1 and c1.col2>=c2.col2 
    group by c1.col1, c1.col2
    order by c1.col1, c1.col2
    go
    drop table test
    This case (which is not prohibited by what we know about the problem so far), is still FAR easier to solve on the client side. It can be solved by creating a temp table with an identity column (to serve as a PK), then applying the same logic that you've used, but it is still easier on the client in my opinion.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Sorry Pat: NOTHING IS EASIER IN CRYSTAL!!!!!
    I know how much everybody just loves Crystal, and I haven't used it in ages so I'm a poor one to ask, but Crystal at least used to provide a function for exactly this purpose (numbering rows within groups). I think that this problem would be easier in Crystal.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Pat Phelan
    I know how much everybody just loves Crystal, and I haven't used it in ages so I'm a poor one to ask, but Crystal at least used to provide a function for exactly this purpose (numbering rows within groups). I think that this problem would be easier in Crystal.

    -PatP
    I aggree with Pat. I use crystal for all my reports and there is a "Row Numbering" filed that can be reset at each group, and doing what the OP wants is very, very simple in CR.

    What does everyone else use for their reporting?

    Mike B

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MikeB_2k4
    What does everyone else use for their reporting?
    Mike B
    Developers...and we pity their cries....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Every time I'm tempted to put any logic or code into Crystal it comes back to bite me on the a$$. I've learned my lesson and just feed it finished output.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    Every time I'm tempted to put any logic or code into Crystal it comes back to bite me on the a$$. I've learned my lesson and just feed it finished output.

    Yes but as far as you can take it...all the business logic in the backend, as much of the rollups I can, cross tabs, ect...

    BUT you had better be absolute in the result set or BOOOOOM Goes the developer code...

    There can be no surprises
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, this is not a stand-alone situation, I've dealt with it in 2 conversions when the old systems did not capture the line numbers for claim details and diagnosis codes. In fact, they WERE using FE to generate the line numbers and display them on the application form (each entry in detail had a time stamp). So when converting to the new system I had to use ClaimID as Col1 and DateEntered as Col2 using exactly the concept I posted earlier (that was the source for INSERT into the new detail table)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Well, this is not a stand-alone situation, I've dealt with it in 2 conversions when the old systems did not capture the line numbers for claim details and diagnosis codes. In fact, they WERE using FE to generate the line numbers and display them on the application form (each entry in detail had a time stamp). So when converting to the new system I had to use ClaimID as Col1 and DateEntered as Col2 using exactly the concept I posted earlier (that was the source for INSERT into the new detail table)
    Did this post end up in the wrong thread somehow ???

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No, it's still about line numbering on the back-end vs. front-end...did I miss something?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Nope, just take 16 a day and you'll be fine....



    EDIT: Now about that registration problem?

    OH NO Mr. Bill...the helpless desk is going to make a site call...HURRY
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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