CODEWITHSUNDEEP
sqlsql-serverdatabase
xar
xar

Reputation: 1439

Getting the whole row from grouped result

Here's a sample database table : 

| ID | ProductID | DateChanged | Price
| 1  | 12        | 2011-11-11  |  93
| 2  | 2         | 2011-11-12  |  12
| 3  | 3         | 2011-11-13  |  25
| 4  | 4         | 2011-11-14  |  17
| 5  | 12        | 2011-11-15  |  97

Basically, what I want to happen is get the latest price of grouped by ProductID.

The result should be like this : 

| ID | ProductID | Price 
| 2  | 2         |  12
| 3  | 3         |  25
| 4  | 4         |  17
| 5  | 12        |  97

If you notice, the first row is not there because there is a new price for ProductID 12 which is the row of ID 5.

Basically, it should be something like get ID,ProductID and Price grouped by productID where DateChanged is the latest.

Upvotes: 0

Views: 74

Answers (4)

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

SELECT  ID, ProductID,DateChanged, Price
FROM    myTable  
WHERE   ID IN
(
    SELECT  MAX(ID)
    FROM    myTable 
    GROUP BY ProductID
)

Upvotes: 1

Raihan
Raihan

Reputation: 10405

SELECT ID, ProductId, Price 
from myTable A
where DateChanged >= all 
  (select DateChanged
   from myTable B
   where B.ID = A.ID);

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17540

SELECT ID, ProductId, Price
FROM
(
   SELECT ID, ProductId, Price
      , ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY DateChanged DESC) AS rowNumber
   FROM yourTable
) AS t
WHERE t.rowNumber = 1

Upvotes: 2

rob mayoff
rob mayoff

Reputation: 385690

select a.id, a.productid, a.price
from mytable a,
    (select productid, max(datechanged) as maxdatechanged
    from mytable
    group by productid) as b
where a.productid = b.productid and a.datechanged = b.maxdatechanged

Upvotes: 0

Related Questions