February 27, 2015 at 9:08 am
Hi. I want to select last order's details for each customer
https://app.box.com/s/05xxi6y8bvfo57csz7wqnjf1rc6hoyrn
I have one table. id is primary key. No Foreign Key
I could select last date and customerid but I want to see quantity, productid, total in last date for each customerid
https://app.box.com/s/w4pdeqykm6k7oq1yxqvnuu12cej9vxi8
Thanks ^_^
February 27, 2015 at 9:31 am
SELECT id, customerid, productid, [date], quantity, total
FROM (
SELECT id, customerid, productid, [date], quantity, total, ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY [date] DESC) rn
FROM Whatever_The_Table_Name_Is
) Numbered
WHERE rn = 1
February 27, 2015 at 9:33 am
ty it works. Is there another solutiton without using ROW_NUMBER() OVER PARTITION BY
February 27, 2015 at 9:34 am
Ranking functions can help you here:
DECLARE @sampleData TABLE (
id int,
customerid int,
productid int,
[date] datetime,
quantity int,
total money
);
INSERT INTO @sampleData VALUES
(1, 1, 1, '20150201', 1, 10),
(2, 1, 2, '20150202', 1, 20),
(3, 1, 3, '20150101', 1, 60),
(4, 2, 1, '20150129', 1, 52),
(5, 2, 2, '20150101', 3, 25),
(6, 2, 2, '20140825', 3, 36),
(7, 3, 2, '20150226', 3, 52),
(8, 3, 3, '20150228', 3, 69);
WITH RankedData AS (
SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY [date] DESC)
FROM @sampleData
)
SELECT *
FROM RankedData
WHERE RN = 1
Next time you post here, include a script of your table definition and sample data as I did in my answer. You'll get faster turnaround and won't require people to type in all your data.
-- Gianluca Sartori
February 27, 2015 at 9:39 am
okay thanks for your help. Im a new bee I will learn the rules
February 27, 2015 at 9:47 am
muho_92 (2/27/2015)
ty it works. Is there another solutiton without using ROW_NUMBER() OVER PARTITION BY
Yes, but they are all very similar and the others likely less efficient and with potential pitfalls.
This one would fail (or show 2 rows for one customer) if 2 rows had the same date
SELECT id, customerid, productid, [date], quantity, total
FROM Whatever_The_Table_Name_Is o
JOIN
(
SELECT id, customerid, max([date]) maxdate
FROM Whatever_The_Table_Name_Is
) i
ON o.customerid=i.customerid
AND o.date=i.maxdate
This one would work too I think:
SELECT id, customerid, productid, [date], quantity, total
FROM Whatever_The_Table_Name_Is o
CROSS APPLY (SELECT TOP 1 id
FROM Whatever_The_Table_Name_Is i
WHERE o.customerid = i.customerid
ORDER BY i.[date] DESC) c
WHERE c.id = o.id
And of course you could do the same with a join instead of an apply. Any reason you don't want to use the windowing function?
Im a new bee I will learn the rules
They aren't so much rules as helpful guidelines. You follow them and someone reading your question can recreate your problem, write a query that solves it, and test against your expected output.
It lets them give you a solution without syntax problems without spending their time constructing an example, and also help clarify the problem you are describing. Thus they can solve it faster, which makes them more likely to help, and able to help faster.
February 27, 2015 at 10:33 am
This one would fail (or show 2 rows for one customer) if 2 rows had the same date
If the system is designed so there are not 2 rows with the same date, but indeed there are 2 rows with the same date, then a duplicate row in the results might be a desired effect. At least it would be for me. Just my two cents though.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 27, 2015 at 11:29 am
Greg Snidow (2/27/2015)
This one would fail (or show 2 rows for one customer) if 2 rows had the same date
If the system is designed so there are not 2 rows with the same date, but indeed there are 2 rows with the same date, then a duplicate row in the results might be a desired effect. At least it would be for me. Just my two cents though.
If it is desired you can use RANK instead of ROW_NUMBER for the first query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply