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?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
 
PortletPaulfreelancerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.