I have a table something like this
userId | productName | transactionId | Date |
---|---|---|---|
6556656 | apple | 3534534 | 25.10 |
6556656 | apple | T423423 | 23.10 |
6556656 | orange | 7687898 | 22.10 |
6556656 | orange | 5675665 | 27.10 |
6556656 | orange | 1231312 | 25.09 |
6556656 | banana | 4564545 | 14.09 |
6556656 | banana | 7898878 | 30.09 |
As you can see I have 7 rows where are 3 kinds of products. I needn't get all 7 rows. I need get only one of each. In result I need only 3 rows where will be only one apple, orange and banana ordered by Date(the most late from each group)
need to write query something like this
var result = _db.Fruits.GroupBy(o => o.ProductName).Select(g => g.OrderByDescending(o => o.Date).FirstOrDefault()
I try any cases but without result.
CodePudding user response:
Quick answer
You want to take the first occurrence of each group.
You can use OrderBy
First
over a GroupBy
:
var top =
db
.Transactions
.GroupBy(
t=> t.Product
)
.Select(t=>new {
t.Key,
date=t.OrderBy(x => x.Date).Select(x=>x.Date).First() // <- Magic is Here!
})
.ToList();
That generates:
SELECT t.Product AS Key, (
SELECT t0.Date
FROM Transactions AS t0
WHERE t.Product = t0.Product
ORDER BY t0.Date
LIMIT 1) AS date
FROM Transactions AS t
GROUP BY t.Product
Note, use OrderByDescending
to get the last Date
(instead the first one)
More elaborate
If you want the whole Transaction
model for each group:
var top =
db
.Transactions
.GroupBy(
t=> t.Product
)
.Select(t=>new {
productname = t.Key,
lasttransaction= t.OrderByDescending(x => x.Date).First()} )
.AsEnumerable() // <-- at this point you should to move sql to client
.Select(t => new {t.productname, t.lasttransaction.Date} )
.ToList();
That is translated as:
SELECT t0.Product, t1.TransactionId, t1.Date, t1.Product
FROM (
SELECT t.Product
FROM Transactions AS t
GROUP BY t.Product
) AS t0
LEFT JOIN (
SELECT t2.TransactionId, t2.Date, t2.Product
FROM (
SELECT t3.TransactionId, t3.Date, t3.Product, ROW_NUMBER() OVER(PARTITION BY t3.Product ORDER BY t3.Date DESC) AS row
FROM Transactions AS t3
) AS t2
WHERE t2.row <= 1
) AS t1 ON t0.Product = t1.Product