September 16, 2007 at 12:52 pm
I'm looking for the first entry of an item in a table that contains the change history of a collection of items. I've thought I was doing it correctly, but have begun to wonder. So, I'm checking here to get some feedback on my code.
Generally, the code I have used is:
select customerId, item, activationDate
from history h1
where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)
using a subquery to find the first entry of the item into the history table.
Do you see problems with this code?
Thanks,
Karen
September 16, 2007 at 1:38 pm
Try this
select customerId, item, min(activationDate) from history group by customerId, item
N 56°04'39.16"
E 12°55'05.25"
September 16, 2007 at 1:40 pm
The two give slightly different results. Can you tell me why?
Thanks,
Karen
September 17, 2007 at 1:33 pm
I'm not sure what specific differences you're seeing, but here's one or two you MIGHT be seeing: your version would allow for "ties", whereas Peter's does not.
I don't know what these items are, but assuming your activation date only has the day component, and someone activated the same item twice in a day - your version would return BOTH records. Peter's is a summary function, so it would return one instance of each person+item, with a date next to it.
Also - you'd see something a little different assuming you had null values in any of those fields.
Are there other differences you're seeing?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 17, 2007 at 2:39 pm
Thank you for your help. I will go forward with this and see where it leads me.
Thanks,
Karen
September 18, 2007 at 2:42 am
See the difference between these two queries
SELECT CustomerID,
Item,
ActivationDate
FROM (
SELECT CustomerID,
Item,
ActivationDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID, Item ORDER BY ActivationDate) AS RecID
FROM History
) AS d
WHERE RecID = 1
SELECT CustomerID,
Item,
ActivationDate
FROM (
SELECT CustomerID,
Item,
ActivationDate,
DENSE_RANK() OVER (PARTITION BY CustomerID, Item ORDER BY ActivationDate) AS RecID
FROM History
) AS d
WHERE RecID = 1
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply