Grouping Records?

Posted on 2005-05-03
Last Modified: 2010-03-19
I have a table named "products" that stores both parent and child products.  A parent product can be assigned several child products.  The table has a field named "fldParent" that determines the products parent.  If fldParent = 0 then the product is a parent.  Otherwise the record is a child record of the product whose id = fldParent. So, I'd like to be able to group/order a listing of products by its parent and child records.  I'd like to be able to order the parents in alphabetical order (based of fldName), and under each parent, have its child records ordered by their fldName.

For Example:

Product A
   Child 1 of Product A
   Child 2 of Product A
Product B
   Child 1 of Product B
   Child 2 of Product B
   Child 3 of Product B
Product C
   Child 1 of Product C

Can this be done in a sql query?


Question by:ingomar
    LVL 68

    Assisted Solution

    If there's only *one* level of parents, it's not too bad:

    SELECT CASE WHEN fldParent = 0 THEN fldName ELSE SPACE(3) + fldName END
    FROM products
    ORDER BY CASE WHEN fldParent = 0 THEN fldName ELSE (SELECT fldName FROM products p2 WHERE p2.fldId = products.fldParent) END,
    CASE WHEN fldParent = 0 THEN '' ELSE fldName END
    LVL 9

    Accepted Solution

    Excuse me Scott,

    with a big tables that select can be very expensive.
    If you use in a subquery, query fields, I think you'll executing a new select for another row.
    you can use

    SELECT CASE WHEN p.fldParent = 0 THEN p.fldName ELSE SPACE(3) + p2.fldName END
    FROM products p left join products p2 on p.fldparent=p2.fldid
    ORDER BY  CASE WHEN p.fldParent = 0 THEN p.fldName ELSE p2.fldName END, case when p.fldParent = 0 THEN '' ELSE p.fldName END

    The good answer is yours.  
    LVL 68

    Expert Comment

    Quite true in general.  I was expecting this table would be keyed on fldId (or whatever the main key name is), so the subquery would require only an index lookup, not a full scan.  Still, the LEFT OUTER JOIN might perform better and is worth trying.
    LVL 2

    Author Comment

    Thanks for your comments. I havent had a chance to try them out yet, but will shortly.

    Thanks again,

    LVL 2

    Author Comment

    That did the trick.  thanks for you help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now