Post reply

Sort Order

  • August 20, 2007 at 5:43 am

    #176288

    Hi I have 2 tables InvoiceDetails and InvoiceSubDetails. (Master detail table) . For each InvoiceDetailID there can be more than 1 InvoiceSubDetails.

    I have to Select few columns from both the tables and the sort order should be by the count of InvoiceSubDetails.

    Eg : InvoiceDetails

    ID Column2 Column3

    1 A AA

    2 B BB

    3 C CC

    For the above Master InvoiceSubDetails

    ID InvoiceDetailID Column3 column4

    1 1 20 AAAA

    2 1 30 BBBB

    3 2 40 CCCC

    4 3 50 DDDD

    5 3 60 EEEE

    6 3 70 FFFF

    I need the result SORTED on the count of InvoiceDetailID in the InvoiceSubDetails

    ie B BB 40 CCCC

    A AA 20 AAAA

    A AA 30 BBBB

    C CC 50 DDDD

    C CC 60 EEEE

    C CC 70 FFFF

    Thanks for the help in advance.

  • August 20, 2007 at 6:08 am

    #727928

    SELECT *

    FROM InvoiceDetails D

        JOIN InvoiceSubDetails S

            ON D.[ID] = S.InvoiceDetailID

        JOIN (

                SELECT S1.InvoiceDetailID

                    ,COUNT(*) AS SubCount

                FROM InvoiceSubDetails S1

                GROUP BY S1.InvoiceDetailID

            ) V

            ON D.[ID] = V.InvoiceDetailID

    ORDER BY V.SubCount

  • August 20, 2007 at 6:09 am

    #727929

    Sudheesh

    Please will you provide some DDL for your tables and let us know what you have tried so far.

    Thanks

    John

    Edit:- Looks like Ken has spared you the trouble!

  • August 20, 2007 at 8:00 am

    #727967

    Try this for a SQL Server 2005 solution

     

    SELECT

    <Col List Here>

    FROM

    (

    SELECT <Col List Here>,

    COUNT(*) OVER (PARTITION BY s.InvoiceDetailID) AS Items

    FROM InvoiceDetails AS d

    INNER JOIN InvoiceSubDetails AS s ON s.InvoiceDetailID = d.ID

    ) AS d

    ORDER

    BY Items DESC

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 21, 2007 at 12:25 am

    #728196

    Thanks for the replies. Its working

Viewing 5 posts - 1 through 5 (of 5 total)

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