Looking for a Result Set
-
Test Data:
CREATE TABLE #Example (
ProductID int,
ProductName varchar(15),
PurchaseDate smalldatetime)
INSERT INTO #Example
SELECT 1, 'Soap', '2007-08-01'
UNION
SELECT 1, 'Brush', '2007-08-02'
UNION
SELECT 2, 'Car', '2007-08-03'
UNION
SELECT 2, 'Truck', '2007-08-04'
UNION
SELECT 3, 'Soap', '2007-08-05'
The result Set I want should look like this:
Brush, 2007-08-02
Truck, 2007-08-04
Soap, 2007-08-05
This is the productname and purchasedate with the highest purchasedate for each productid.
-
I think I just figured this out. This at least works with this small data set.
Select ProductName, #Example.PurchaseDate
FROM #Example
JOIN
(SELECT ProductID, Max(PurchaseDate) AS PurchaseDate
FROM #Example
GROUP BY ProductID) AS Bob
ON #Example.PurchaseDate = Bob.PurchaseDate
Can anyone see a flaw?
-
Yes, I do... why do you have different product names for the same ProductID's?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's just an example. This data does not mirror the actual data but does mirror some of the issues. The actual tables is a list of different status events. Order 1 can have status x and time a. Later Order 1 can now have status y and time b. All I want is the last status and time.
-
If the data "mirrors some of the issues", then the data might really have a problem.
Yes, your code does work... but it's a pretty bad example because the ProductID should be unique... that why it's call an "ID".--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The question presented was not "how should I label my columns?"
-
No... but if you want good help... you have to ask better questions and provide a more reasonable representation of what your data actually looks like. You'll also find that a lot of folks on this forum will try to help you with problems that you didn't necessarily ask for help on, if they see them. Looks to me like you either didn't spend much time formulating your example data or you have a really bad problem with your real data. Also looks like you have a pretty hefty chip on your shoulder... You'll do much better if you leave that chip at home.
Good luck, David. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply