Post reply

TOP FUNCTION

  • December 18, 2007 at 2:29 am

    #98689

    Hi

    I need an urgent query to retrieve top 2 invoices for each account. An account can have 'n' number of invoices. but i need a query which will retrieve top 2 invoices for each account.

    Is there any single query???????

  • December 18, 2007 at 3:32 am

    #760932

    Yes.

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

  • December 18, 2007 at 3:33 am

    #760934

    What defines which invoices are considered top for each account? Invoice daet? Amount? Number of items ordered?

    Can you please post the schema of the tables involved, some sample data and what you want the outout to be.

    See Forum Etiquette[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2007 at 4:12 am

    #760941

    Example :

    inv acc date

    -------------------

    inv1 acc1 10/12/2007

    inv2 acc1 11/12/2007

    inv3 acc1 12/12/2007

    inv4 acc2 11/12/2007

    inv5 acc2 12/12/2007

    inv6 acc3 13/12/2007

    output should be as follows:-

    inv2 acc1 11/12/2007

    inv3 acc1 12/12/2007

    inv5 acc2 12/12/2007

    inv6 acc3 13/12/2007

  • December 18, 2007 at 4:51 am

    #760943

    Why would you not like this output?

    inv2 acc1 11/12/2007

    inv3 acc1 12/12/2007

    inv4 acc2 11/12/2007

    inv5 acc2 12/12/2007

    inv6 acc3 13/12/2007

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

  • December 18, 2007 at 5:47 am

    #760958

    i need to select only the latest 2 invoices for that account so inv4 should not be retrieved.

  • December 18, 2007 at 5:52 am

    #760959

    I see that you need an "urgent" query. Does that mean this is homework? What have you tried so far?

    John

  • December 18, 2007 at 5:58 am

    #760964

    Ananth (12/18/2007)

    i need to select only the latest 2 invoices for that account so inv4 should not be retrieved.

    In the sample data you gave it is one of the latest 2, in fact one of the only two, invoices for acct2

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2007 at 6:30 am

    #760974

    YES, I NEED ONLY LATEST 2 INVOICE FOR THAT ACCOUNT....DO YOU HAVE THE QUERY?????

  • December 18, 2007 at 6:39 am

    #760976

    i used cursors for the above result.... but i need to avoid the cusrsor????

  • December 18, 2007 at 6:41 am

    #760978

    When is the assignment due?

    Don't worry. You will have a suggestion before that.

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

  • December 18, 2007 at 6:45 am

    #760982

    DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    SET DATEFORMAT DMY

    INSERT@Sample

    SELECT'inv1', 'acc1', '10/12/2007' UNION ALL

    SELECT'inv2', 'acc1', '11/12/2007' UNION ALL

    SELECT'inv3', 'acc1', '12/12/2007' UNION ALL

    SELECT'inv4', 'acc2', '11/12/2007' UNION ALL

    SELECT'inv5', 'acc2', '12/12/2007' UNION ALL

    SELECT'inv6', 'acc3', '13/12/2007'

    SELECTs.Account,

    MAX(s.Date) AS Date

    FROM@Sample AS S

    WHERENOT EXISTS(

    SELECTw.Account,

    MAX(w.Date)

    FROM@Sample AS w

    GROUP BYw.Account

    HAVINGs.Account = w.Account

    AND s.Date = MAX(w.Date)

    )

    GROUP BYs.Account

    UNION ALL

    SELECTw.Account,

    MAX(w.Date)

    FROM@Sample AS w

    GROUP BYw.Account

    ORDER BYAccount,

    Date DESC

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

  • December 18, 2007 at 7:15 am

    #761007

    Thanks peter!!!!!!!!

    Itz really useful for me. Now the above query will return latest 2 invoices for that account. if i need to return latest 10 or 12 invoices for that account.......what modification should be done in the above query.....

  • December 18, 2007 at 7:27 am

    #761013

    -- Prepare sample data

    DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    SET DATEFORMAT DMY

    INSERT@Sample

    SELECT'inv1', 'acc1', '10/12/2007' UNION ALL

    SELECT'inv2', 'acc1', '11/12/2007' UNION ALL

    SELECT'inv3', 'acc1', '12/12/2007' UNION ALL

    SELECT'inv4', 'acc2', '11/12/2007' UNION ALL

    SELECT'inv5', 'acc2', '12/12/2007' UNION ALL

    SELECT'inv6', 'acc3', '13/12/2007'

    -- Create staging area

    DECLARE@Stage TABLE (RowID INT IDENTITY, Invoice CHAR(4), Account CHAR(4), Date DATETIME)

    INSERT@Stage

    (

    Invoice,

    Account,

    Date

    )

    SELECTInvoice,

    Account,

    Date

    FROM@Sample

    ORDER BYAccount,

    Date DESC

    DECLARE@NumberOfRecordsPerAccount INT

    SET@NumberOfRecordsPerAccount = 10

    -- Show the expected output

    SELECTs.Invoice,

    s.Account,

    s.Date

    FROM@Stage AS s

    INNER JOIN(

    SELECTMIN(RowID) AS MinRow,

    MIN(RowID) + @NumberOfRecordsPerAccount AS MaxRow,

    Account

    FROM@Stage

    GROUP BYAccount

    ) AS d ON d.Account = s.Account

    WHEREs.RowID >= d.MinRow

    AND s.RowID < d.MaxRow

    ORDER BYs.Account,

    s.Date DESC

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

  • December 18, 2007 at 7:36 am

    #761020

    Thanks Peter!!!

Viewing 15 posts - 1 through 15 (of 20 total)

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