[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

SQL SELECT Max

Hello,
I'm trying to use below code to find the last Invoice number (Highest) per client.

Results I'm getting is all Invoices / per client.

Can you please help.

Select (Select Name From Clients where Clients.AccountNumber = AROpentransactions.AccountNumber) AS [Client],
AccountNumber, MAX (TransactionDate), TransactionNumber,OrdersSubtotalAmount,SalesAmount,TaxAmount,ARAmount,
(Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.AccountNumber) AS [Accountlocked]  
from ARopentransactions
where (Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.AccountNumber) = 1
AND TransactionType = 1
GROUP BY AccountNumber,TransactionNumber,OrdersSubtotalAmount,SalesAmount,TaxAmount,ARAmount

Thanks,
0
W.E.B
Asked:
W.E.B
  • 3
  • 3
  • 2
1 Solution
 
PortletPaulCommented:
please try this
SELECT
      Clients.NAME AS [Client]
    , OT.AccountNumber
    , OT.TransactionDate
    , OT.TransactionNumber
    , OT.OrdersSubtotalAmount
    , OT.SalesAmount
    , OT.TaxAmount
    , OT.ARAmount
    , Clients.Accountlocked
FROM ARopentransactions as OT
INNER JOIN (
            SELECT
              AccountNumber
            , row_number() over (partition by AccountNumber order by TransactionDate DESC) as row_ref
            FROM ARopentransactions
            WHERE TransactionType = 1
           ) AS Latest ON OT.AccountNumber = Latest.AccountNumber
                      AND OT.TransactionDate = Latest.TransactionDate
INNER JOIN Clients
        ON  OT.AccountNumber = Clients.AccountNumber
WHERE Latest.row_ref = 1

Open in new window

0
 
W.E.BAuthor Commented:
Hello Paul,
I get error

Msg 207, Level 16, State 1, Line 19
Invalid column name 'TransactionDate'.

at this line
           ) AS Latest ON OT.AccountNumber = Latest.AccountNumber
                      AND OT.TransactionDate = Latest.TransactionDate

thanks
0
 
PortletPaulCommented:
forgot an entry in that query (line 15)
SELECT
      Clients.NAME AS [Client]
    , OT.AccountNumber
    , OT.TransactionDate
    , OT.TransactionNumber
    , OT.OrdersSubtotalAmount
    , OT.SalesAmount
    , OT.TaxAmount
    , OT.ARAmount
    , Clients.Accountlocked
FROM ARopentransactions as OT
INNER JOIN (
            SELECT
              AccountNumber
            , TransactionDate
            , row_number() over (partition by AccountNumber order by TransactionDate DESC) as row_ref
            FROM ARopentransactions
            WHERE TransactionType = 1
           ) AS Latest ON OT.AccountNumber = Latest.AccountNumber
                      AND OT.TransactionDate = Latest.TransactionDate
INNER JOIN Clients
        ON  OT.AccountNumber = Clients.AccountNumber
WHERE Latest.row_ref = 1

Open in new window

is there a unique row identifier in table ARopentransactions? if there is this would be more efficient
SELECT
      Clients.NAME AS [Client]
    , OT.AccountNumber
    , OT.TransactionDate
    , OT.TransactionNumber
    , OT.OrdersSubtotalAmount
    , OT.SalesAmount
    , OT.TaxAmount
    , OT.ARAmount
    , Clients.Accountlocked
FROM ARopentransactions as OT
INNER JOIN (
            SELECT
              ID --<< unique identiry of this table
            , TransactionDate
            , row_number() over (partition by AccountNumber order by TransactionDate DESC) as row_ref
            FROM ARopentransactions
            WHERE TransactionType = 1
           ) AS Latest ON OT.ID = Latest.ID --<< unique identiry of this table
INNER JOIN Clients
        ON  OT.AccountNumber = Clients.AccountNumber
WHERE Latest.row_ref = 1

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Anthony PerkinsCommented:
I suspect you may find the derived table is just missing the TransactionDate column (but no points if I am right).  In other words, it should be:
SELECT  Clients.NAME AS [Client],
        OT.AccountNumber,
        OT.TransactionDate,
        OT.TransactionNumber,
        OT.OrdersSubtotalAmount,
        OT.SalesAmount,
        OT.TaxAmount,
        OT.ARAmount,
        Clients.Accountlocked
FROM    ARopentransactions AS OT
        INNER JOIN (SELECT  AccountNumber,
                            TransactionDate,
                            ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY TransactionDate DESC) AS row_ref
                    FROM    ARopentransactions
                    WHERE   TransactionType = 1
                   ) AS Latest ON OT.AccountNumber = Latest.AccountNumber
                                  AND OT.TransactionDate = Latest.TransactionDate
        INNER JOIN Clients ON OT.AccountNumber = Clients.AccountNumber
WHERE   Latest.row_ref = 1

Open in new window

0
 
Anthony PerkinsCommented:
Oops, sorry about that.  I did not see your last comment.
0
 
PortletPaulCommented:
:) no problem, & you spotted the ommission
0
 
W.E.BAuthor Commented:
Paul,
As always,
Thank you.
0
 
W.E.BAuthor Commented:
Thank you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now