Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: grouping results of group by?

    Hi,
    I have a table on which I can execute group by and get back a set of results like these:

    Name1 SessionId1
    Name2 SessionId1
    Name3 SessionId1
    Name4 SessionId2
    Name5 SessionId2
    Name6 SessionId2

    the problem is, I need to use these rows by grouping them again in my application. all rows with sessionId1 should be under a container type instance (like an arraylist in java), and all rows with sessionId2 should go under another container instance.

    Is there any way I can use sql to get back some sort of containers? Can I use a custom type for example? If I can handle this in the db, that'd really save me a lot of cpu processing, but my last contact with db layer has been a while ago Your response would be appreciated a lot

    Kind regards
    Seref

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's a container? i don't believe an ANSI SQL database has any of dem

    while i did not understand our question, there is no reason you couldn't use a GROUP BY in a query where the FROM clause specifies your original query as a derived table subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by sarikan
    Hi,
    I have a table on which I can execute group by and get back a set of results like these:

    Name1 SessionId1
    Name2 SessionId1
    Name3 SessionId1
    Name4 SessionId2
    Name5 SessionId2
    Name6 SessionId2

    the problem is, I need to use these rows by grouping them again in my application. all rows with sessionId1 should be under a container type instance (like an arraylist in java), and all rows with sessionId2 should go under another container instance.

    Is there any way I can use sql to get back some sort of containers? Can I use a custom type for example? If I can handle this in the db, that'd really save me a lot of cpu processing, but my last contact with db layer has been a while ago
    If I understand well, you want some kind of "aggregate concatenation": grouping by SessionID, such that the name field for SessionID1 contains 'Name1'||','||'Name2'||','||'Name3'.
    That's the closest you can come to a "list container" in a relational context, since table cells have to be "atomic", i.e., cannot be composite.

    Searching for "aggregate concatenation" in this forum yields e.g. http://www.dbforums.com/ansi-sql/163...ml#post6352492 which in turn points to a worked-out example: http://www.dbforums.com/ansi-sql/161...ml#post6248263
    Note that this requires that your database supports recursive CTEs, a relatively new SQL syntax feature.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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