sql query get row from max of group

Here is some sample data from my sql server 2012 orderdata table


CREATE TABLE #OrderData
(orderid int IDENTITY(1,1)  NOT NULL
       ,Custid   INT
      ,ProductType  VARCHAR(100)
      ,OrderAmount  INT
      ,OrderDate DATETIME
)
GO

INSERT INTO #OrderData VALUES
(1   ,          'Toy'           ,       200        ,             '1/2/2016'),        ----orderid 1
(1   ,          'Kitchen'       ,    300           ,          '6/2/2016'),        ----orderid 2
(5   ,         'LivingRoom'     ,700               ,      '3/31/2016'),      ----orderid 3
(1   ,         'Toy'            ,       300        ,             '10/2/2016'),      ----orderid 4
(5   ,         'LivingRoom'     ,200               ,     '3/31/2015')        ----orderid 5

(Note: in example the order1d starts at 1 and is incremented by 1)

Trying to write a query that will return the row with highest order id per product type

Given the sample data

Would like the output to be


orderid      custid        producttype     orderamount      orderdate
2                    1              Kitchen                300                      2016-06-02 00:00:00.000
4                    1              Toy                        300                      2016-10-02 00:00:00.000
5                    5             LivingRoom        200                      2015-03-31 00:00:00.000
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Pawan KumarDatabase ExpertCommented:
Please use this solution

For LivingRoom I think this row needs to be picked, your output has a typo.

Explanation - we can use row__number logic with partition by to get the numbering..

SOLUTION

SELECT orderid     , custid,        producttype ,    orderamount   ,   orderdate
FROM
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Custid,ProductType ORDER BY OrderDate DESC) rnk FROM #OrderData
)r WHERE rnk = 1

Open in new window



OUTPUT
/*------------------------

SELECT orderid     , custid,        producttype ,    orderamount   ,   orderdate
FROM
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Custid,ProductType ORDER BY OrderDate DESC) rnk FROM #OrderData
)r WHERE rnk = 1
------------------------*/
orderid     custid      producttype     orderamount orderdate
----------- ----------- --------------- ----------- -----------------------
2           1           Kitchen         300         2016-06-02 00:00:00.000
4           1           Toy             300         2016-10-02 00:00:00.000
3           5           LivingRoom      700         2016-03-31 00:00:00.000

(3 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Another solution

SELECT o.* FROM 
(
	SELECT custid,        producttype ,     MAX(orderdate) orderdate
	FROM #OrderData
	GROUP BY Custid,ProductType
)i INNER JOIN #OrderData o ON o.Custid = i.Custid AND o.ProductType = i.ProductType 
	AND i.orderdate = o.OrderDate
ORDER BY i.Custid,o.orderid

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
orderid     Custid      ProductType                                                                                          OrderAmount OrderDate
----------- ----------- ---------------------------------------------------------------------------------------------------- ----------- -----------------------
2           1           Kitchen                                                                                              300         2016-06-02 00:00:00.000
4           1           Toy                                                                                                  300         2016-10-02 00:00:00.000
3           5           LivingRoom                                                                                           700         2016-03-31 00:00:00.000

(3 row(s) affected)

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
The best solution for returning whole rows associated with concepts such as "the highest" uses row_number() over()

Your requirement is that for each producttype the highest orderid is returned  which "translates" into

             row_number() over(partition by producttype order by orderid DESC)

SELECT
      orderid, custid, producttype, orderamount, orderdate
FROM (
      SELECT
            *
          , ROW_NUMBER()
              OVER(PARTITION BY producttype ORDER BY orderid DESC) rn
      FROM #OrderData
      ) d
WHERE rn = 1

Open in new window

Note that the DESCending order of orderid ensures that the value of 1 is assigned to the highest orderid, the partition by ensures that is done for each producttype

Using row_number() over() is helpful for "the latest" or "the earliest" etc.  and when you need the whole rows meeting that conditin.
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
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Pawan KumarDatabase ExpertCommented:
Sorry my bad typo ..order by should on order id

SELECT orderid     , custid,        producttype ,    orderamount   ,   orderdate
FROM
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY custid,ProductType ORDER BY orderid DESC) rnk FROM #OrderData
)r WHERE rnk = 1

Open in new window


OUTPUT

/*------------------------

SELECT orderid     , custid,        producttype ,    orderamount   ,   orderdate
FROM
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY custid,ProductType ORDER BY orderid DESC) rnk FROM #OrderData
)r WHERE rnk = 1
------------------------*/
orderid     custid      producttype        orderamount orderdate
----------- ----------- ------------------ ----------- -----------------------
2           1           Kitchen            300         2016-06-02 00:00:00.000
4           1           Toy                300         2016-10-02 00:00:00.000
5           5           LivingRoom         200         2015-03-31 00:00:00.000

(3 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
updated 2nd solution also

/*------------------------

SELECT o.* FROM 
(
	SELECT custid,        producttype ,     MAX(OrderId) OrderId
	FROM #OrderData
	GROUP BY Custid,ProductType
)i INNER JOIN #OrderData o ON o.Custid = i.Custid AND o.ProductType = i.ProductType 
	AND i.OrderId = o.OrderId
ORDER BY i.Custid,o.orderid
------------------------*/
orderid     Custid      ProductType       OrderAmount OrderDate
----------- ----------- ----------------- ----------- -----------------------
2           1           Kitchen           300         2016-06-02 00:00:00.000
4           1           Toy               300         2016-10-02 00:00:00.000
5           5           LivingRoom        200         2015-03-31 00:00:00.000

(3 row(s) affected)

Open in new window

0
ZberteocCommented:
Use this:
select t.* from #OrderData t
where orderid = (select top 1 s.orderid from #OrderData s where s.ProductType=t.ProductType order by s.orderid desc)

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Zberteoc, the expected results show 3 different orderids, the query above can only return a single orderid

Would like the output to be
orderid      custid        producttype     orderamount      orderdate
2                    1              Kitchen                300                      2016-06-02 00:00:00.000
4                    1              Toy                        300                      2016-10-02 00:00:00.000
5                    5             LivingRoom        200                      2015-03-31 00:00:00.000
0
johnnyg123Author Commented:
thanks!
0
Pawan KumarDatabase ExpertCommented:
@johnnyg123

What about my comments ? You have not replied on them ?
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
SQL

From novice to tech pro — start learning today.