March 4, 2013 at 12:53 am
Hi all
This seems like a simple thing to be able to do but I am really struggling with it
Some DDL
create table table1
(name nvarchar(50), purchase_date datetime,product nvarchar(50),quantity int)
insert into table1
values
('John','2013-03-02 14:53:47.000','Car','2'),
('Mike','2013-03-02 15:04:17.000','Car','3'),
('John','2013-03-02 15:04:29.000','Car','5')
How can I get the following select statement to work?
select name,MAX(purchase_date),product,quantity
from table1
group by name
What I need returned is a distinct list of the names with the max purchase_date, including the product and the quantity for that specific max purchase date
Is this possible with a group by statement?
Thanks
March 4, 2013 at 12:58 am
Not really possible with a GROUP BY because you want two columns off the row with the max date, so use a window function:
create table #table1
(name nvarchar(50), purchase_date datetime,product nvarchar(50),quantity int)
insert into #table1
values
('John','2013-03-02 14:53:47.000','Car','2'),
('Mike','2013-03-02 15:04:17.000','Car','3'),
('John','2013-03-02 15:04:29.000','Car','5')
SELECT name, purchase_date, product, quantity
FROM (
SELECT name, purchase_date, product, quantity
,n=ROW_NUMBER() OVER (PARTITION BY name ORDER BY purchase_date DESC)
FROM #table1) a
WHERE n = 1
DROP TABLE #table1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2013 at 1:31 am
dwain.c (3/4/2013)
Not really possible with a GROUP BY because you want two columns off the row with the max date, so use a window function:
create table #table1
(name nvarchar(50), purchase_date datetime,product nvarchar(50),quantity int)
insert into #table1
values
('John','2013-03-02 14:53:47.000','Car','2'),
('Mike','2013-03-02 15:04:17.000','Car','3'),
('John','2013-03-02 15:04:29.000','Car','5')
SELECT name, purchase_date, product, quantity
FROM (
SELECT name, purchase_date, product, quantity
,n=ROW_NUMBER() OVER (PARTITION BY name ORDER BY purchase_date DESC)
FROM #table1) a
WHERE n = 1
DROP TABLE #table1
Thanks
Quick question - Why doesn't this work?:
SELECT name, purchase_date, product, quantity
,n = ROW_NUMBER() OVER (PARTITION BY name ORDER BY purchase_date DESC)
FROM table1 where n = 1
Thanks
March 4, 2013 at 1:36 am
SQLSACT (3/4/2013)
ThanksQuick question - Why doesn't this work?:
SELECT name, purchase_date, product, quantity,n = ROW_NUMBER() OVER (PARTITION BY name ORDER BY purchase_date DESC)
FROM table1 where n = 1
Thanks
You can't use the column n in the WHERE clause because n is unknown at that point. You cannot either put the ROW_NUMBER() into that WHERE clause. So normally everyone just uses a derived table (like me) or put the derived table into a CTE.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2013 at 1:43 am
Thanks Dwain
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply