SQL Syntax sum by highest version needed

I have a table with a structure very similar to this:

ORDRNMBR,ORDRVRSN,ORDRDATE,ORDRAMNT where in this case each order may have multiple versions and hence the need for the ORDRVRSN field.

If it were not for that field a simple SELECT SUM(ORDRAMNT) would do. However for the 5% of the orders that do have multiple versions when it comes to summing I only want the order amount associated with the highest version number. So my question is what is the proper syntax to only include the highest version number for these orders while also including all the other orders in the total.
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
I'm not sure why you need a sum if there's a single field with the total amount for the order: does the table hold individual line items? The preferred method for getting the Top N of a group is with a CTE using either the RANK() or ROW_NUMBER() functions. RANK will give rows the same rank number if they have the same rank (it's a tie), while row number will give each line a unique number.
; WITH cteOrders
AS (
    SELECT 
        ORDRNMBR
        ,ORDRVRSN
        ,ORDRDATE
        ,ORDRAMNT
        , ROW_NUMBER() OVER (PARTITION BY ORDRNMBR ORDER BY ORDRVRSN DESK) AS VersionRank
    FROM ORDER_TABLE
)
SELECT 
        ORDRNMBR
        ,ORDRVRSN
        ,ORDRDATE
        ,SUM(ORDRAMNT) AS ORDRAMNT_TOTAL
FROM cteOrders
WHERE VersionRank = 1

Open in new window

0
rwheeler23Author Commented:
The issue is I only want one of the amounts included in the total. For example:

ORDRNMBR,ORDRVRSN,ORDRDATE,ORDRAMNT
1000,0,12/31/15,1000
1001,0,12/30/15,2000
1002,0,12/29/15,3000
1002,1,12/29/15,4000

So the sum needs to include only 1000,2000 and 4000 because order 1002 has a second version so I only want the highest version amount which is 4000. The 3000 is not to be included because it is the first version of this order.
0
Russell FoxDatabase DeveloperCommented:
That should work then. If you just want the sum, remove the other fields:
; WITH cteOrders
AS (
    SELECT 
        ORDRNMBR
        ,ORDRVRSN
        ,ORDRDATE
        ,ORDRAMNT
        , ROW_NUMBER() OVER (PARTITION BY ORDRNMBR ORDER BY ORDRVRSN DESK) AS VersionRank
    FROM ORDER_TABLE
)
SELECT SUM(ORDRAMNT) AS ORDRAMNT_TOTAL
FROM cteOrders
WHERE VersionRank = 1

Open in new window

If you want to see what the ROW_NUMBER is producing, select everything from the CTE to make sure the new VersionRank field is really giving you what you want to see:
; WITH cteOrders
AS (
    SELECT 
        ORDRNMBR
        ,ORDRVRSN
        ,ORDRDATE
        ,ORDRAMNT
        , ROW_NUMBER() OVER (PARTITION BY ORDRNMBR ORDER BY ORDRVRSN DESK) AS VersionRank
    FROM ORDER_TABLE
)
SELECT *
FROM cteOrders
ORDER BY ORDRNMBR, ORDRVRSN, VersionRank

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
"DESK" should be DESC in the above

It isn't essential (or in my view even desirable) to use a CTE because a simple "derived table" provides the same result.
SELECT
      *
FROM (
      SELECT
            ORDRNMBR
          , ORDRVRSN
          , ORDRDATE
          , ORDRAMNT
          , ROW_NUMBER() OVER (PARTITION BY ORDRNMBR 
                              ORDER BY ORDRVRSN DESC) AS VersionRank
      FROM ORDER_TABLE
    ) AS derived
ORDER BY
      ORDRNMBR
    , ORDRVRSN
    , VersionRank

Open in new window

no points please
0
Chris LuttrellSenior Database ArchitectCommented:
either way you do it, if you only want the latest version in the results, just add a where clause to return where the rank = 1
CREATE TABLE #sample (ORDRNMBR INT,ORDRVRSN INT,ORDRDATE DATE,ORDRAMNT MONEY);
INSERT INTO #sample ( ORDRNMBR, ORDRVRSN, ORDRDATE, ORDRAMNT )
VALUES
(1000,0,'12/31/15',1000),
(1001,0,'12/30/15',2000),
(1002,0,'12/29/15',3000),
(1002,1,'12/29/15',4000)

SELECT * FROM #sample S

SELECT
      *
FROM (
      SELECT
            ORDRNMBR
          , ORDRVRSN
          , ORDRDATE
          , ORDRAMNT
          , RANK() OVER (PARTITION BY ORDRNMBR 
                              ORDER BY ORDRVRSN DESC) AS VersionRank
      FROM #sample
    ) AS derived
WHERE derived.VersionRank = 1
ORDER BY
      ORDRNMBR
    , ORDRVRSN
    , VersionRank

DROP TABLE #sample

Open in new window

and your results would be
ORDRNMBR	ORDRVRSN	ORDRDATE	ORDRAMNT	VersionRank
1000	0	2015-12-31	1000.00	1
1001	0	2015-12-30	2000.00	1
1002	1	2015-12-29	4000.00	1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
The query by Russell Fox which does - correctly - include the where clause for versionrank = 1

I forgot to include it - sorry
all I wanted to point out is that a derived table can be used, and that a CTE isn't mandatory.

RANK() can result in more than one row (within a single partition) sharing the same value

ROW_NUMBER() does not do that. I would stay with row_number myself.
0
rwheeler23Author Commented:
Thank you all for your input on this. Depending on who is working on any given day, this query may be run repeatedly and has to parse thousands of records.  Is there any appreciable performance difference between these two approaches?
0
Russell FoxDatabase DeveloperCommented:
A better approach might be to add a flag to the table, IsActive, BIT, default = 1, and when you create a new version of an order you deactivate any prior versions. Then in your query just add "WHERE IsActive = 1".
0
rwheeler23Author Commented:
Let me investigate the ramifications of doing that.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.