I need to get rows without dupicate ProductID. In the below query, the
subquery returns 1 row, which is fine. But the query itself returns 2 rows
because the TransactionDate has two same date. I want the quesry to return
only one row if it has the same ProductID.
TransactionID is the primary key, which determines the
uniqueness of rows. Qty is not important in the query, but we only need to
have it in the select. We query based on ProductID,StoreID and the date. We
want to get each row based on ProductID, and StoreID which have the
MaX(TransactionDate). Each row returned must have only one ProductID and
StoreID.
For example,
TransactionID ProductID StoreID QTY Date
1 Prd1 2 4
1/2/2005 9:32AM
2 Prd1 2 8
1/2/2005 9:32AM
3 Prd5 2 9
3/2/2005 9:33AM
4 Prd5 4 9
4/2/2005 9:33AM
If we want to get all the products which are in StoreID 2 within the date
that fall below 3/2/2005 10:00AM it must returne the following rows:
TransactionID ProductID StoreID QTY Date
2 Prd1 2 8
1/2/2005 9:32AM
3 Prd5 2 9
3/2/2005 9:33AM
Why? Because we want the last entered record for the ProductID in the
StoreID Within the date. If we see the subquery, this is right which return
s
the right record, but the main query returnes the other record as well, that
is TransactionID #1.
But when we join it with the main query we get wrong results, that is we get
TransactionID #1,2,3. We only want TransactionID #2, #3.
Note:
We cannot use Top 1 in the subquery, because we want to query for many
products which only returns one record for each ProductID. For example, if
there are ProductID 1,2,3,and 4 it must check for all the products if the
query does not specify the ProductID as a limit. It may even want to get all
products from all
stores within specified date. But it must only get 1 Same ProductID for ever
y
StoreID as the previouse example says.
The subquery below works fine, but I think the problem is in the outer query
.
Here is the query:
SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
FROM [Inventory Transactions] IT
LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
INNER JOIN (
SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
10:02:26'
AND StoreID IN(2) GROUP BY
Products.ProductID,StoreID) AS X
ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
IT.TransactionDate=X.MaxDate
ORDER BY Products.ProductName
MikeDon't have time for a full explanation, but try adding
HAVING TransactionDate = MAX(TransactionDate)
after the GROUP BY clause in your subquery. This should force it to return
only the row for the most recent sale.
"Mike9900" wrote:
> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to retur
n
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which retu
rns
> the right record, but the main query returnes the other record as well, th
at
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we g
et
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example, i
f
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get a
ll
> products from all
> stores within specified date. But it must only get 1 Same ProductID for ev
ery
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer que
ry.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Forget my earlier post. Could you post DDL for all tables involved in the
query, and sample data?
"Mike9900" wrote:
> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to retur
n
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which retu
rns
> the right record, but the main query returnes the other record as well, th
at
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we g
et
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example, i
f
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get a
ll
> products from all
> stores within specified date. But it must only get 1 Same ProductID for ev
ery
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer que
ry.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Hi
Your problem is that the MaxDate is not unique for each ProductID AND
StoreID combination in [Inventory Transactions] (the accuracy of a datetime
value is one three-hundredth of a second). If TransactionID is guaranteed to
be ascending over time then you could use this instead of the date.
e.g (untested)
SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
FROM [Inventory Transactions] IT
LEFT JOIN Products P ON P.ProductID=IT.ProductID
JOIN (
SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
FROM [Inventory Transactions] T
LEFT JOIN Products Q ON Q.ProductID=T.ProductID
WHERE T.TransactionDate<='03/02/2005 10:02:26'
AND StoreID IN(2)
GROUP BY Q.ProductID,T.StoreID) AS X
ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
IT.TransactionID=X.MaxTID
ORDER BY P.ProductName
You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on how
to post DDL and example data that can be usable.
John
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
> I need to get rows without dupicate ProductID. In the below query, the
> subquery returns 1 row, which is fine. But the query itself returns 2 rows
> because the TransactionDate has two same date. I want the quesry to
> return
> only one row if it has the same ProductID.
> TransactionID is the primary key, which determines the
> uniqueness of rows. Qty is not important in the query, but we only need to
> have it in the select. We query based on ProductID,StoreID and the date.
> We
> want to get each row based on ProductID, and StoreID which have the
> MaX(TransactionDate). Each row returned must have only one ProductID and
> StoreID.
> For example,
> TransactionID ProductID StoreID QTY Date
> 1 Prd1 2 4
> 1/2/2005 9:32AM
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> 4 Prd5 4 9
> 4/2/2005 9:33AM
> If we want to get all the products which are in StoreID 2 within the date
> that fall below 3/2/2005 10:00AM it must returne the following rows:
> TransactionID ProductID StoreID QTY Date
> 2 Prd1 2 8
> 1/2/2005 9:32AM
> 3 Prd5 2 9
> 3/2/2005 9:33AM
> Why? Because we want the last entered record for the ProductID in the
> StoreID Within the date. If we see the subquery, this is right which
> returns
> the right record, but the main query returnes the other record as well,
> that
> is TransactionID #1.
>
> But when we join it with the main query we get wrong results, that is we
> get
> TransactionID #1,2,3. We only want TransactionID #2, #3.
> Note:
> We cannot use Top 1 in the subquery, because we want to query for many
> products which only returns one record for each ProductID. For example,
> if
> there are ProductID 1,2,3,and 4 it must check for all the products if the
> query does not specify the ProductID as a limit. It may even want to get
> all
> products from all
> stores within specified date. But it must only get 1 Same ProductID for
> every
> StoreID as the previouse example says.
>
> The subquery below works fine, but I think the problem is in the outer
> query.
> Here is the query:
> SELECT DISTINCT IT.ProductID, IT.StoreID,IT.Quantity, Products.ProductName
> FROM [Inventory Transactions] IT
> LEFT OUTER JOIN Products Products ON Products.ProductID=IT.ProductID
> INNER JOIN (
> SELECT Products.ProductID,StoreID,MAX(TransactionDate) MaxDate
> FROM [Inventory Transactions] IT LEFT OUTER JOIN Products Products
> ON Products.ProductID=IT.ProductID WHERE TransactionDate<='03/02/2005
> 10:02:26'
> AND StoreID IN(2) GROUP BY
> Products.ProductID,StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionDate=X.MaxDate
> ORDER BY Products.ProductName
> --
> Mike|||Thanks for the answer.
TransactionID is the primary key that is incremented by the system. It is
gurantedd I think, don't you?
--
Mike
"John Bell" wrote:
> Hi
> Your problem is that the MaxDate is not unique for each ProductID AND
> StoreID combination in [Inventory Transactions] (the accuracy of a datetim
e
> value is one three-hundredth of a second). If TransactionID is guaranteed
to
> be ascending over time then you could use this instead of the date.
> e.g (untested)
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> LEFT JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionID=X.MaxTID
> ORDER BY P.ProductName
> You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on h
ow
> to post DDL and example data that can be usable.
> John
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
>
>|||The other tables are Store with StoreID as primary key, Products with
ProductID as the primary key. These two keys are references in this table.
--
Mike
"Mark Williams" wrote:
> Forget my earlier post. Could you post DDL for all tables involved in the
> query, and sample data?
> "Mike9900" wrote:
>|||Thanks for the help.
We want it based on TransactionDate not the TransactionID. There could have
been a transaction that had lower date but the TransactionID is the highest.
This occurs, for example, when the user wants to post a transaction for
previous month today. If the user post the previouse month transaction today
,
TransactionID is Max but the TransactionDate is not Max.
We want a combination of both Max(TransactionDate) and Max(TransactionID) to
be true, but here we see that the TransactionDate could not be unique and
TransactionID is not correct by itself.
--
Mike
"John Bell" wrote:
> Hi
> Your problem is that the MaxDate is not unique for each ProductID AND
> StoreID combination in [Inventory Transactions] (the accuracy of a datetim
e
> value is one three-hundredth of a second). If TransactionID is guaranteed
to
> be ascending over time then you could use this instead of the date.
> e.g (untested)
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> LEFT JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionID) AS MaxTID
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X
> ON IT.ProductID=X.ProductID AND IT.StoreID=X.StoreID AND
> IT.TransactionID=X.MaxTID
> ORDER BY P.ProductName
> You may want to check out http://www.aspfaq.com/etiquette.asp?id=5006 on h
ow
> to post DDL and example data that can be usable.
> John
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:E7A4AC26-DF6E-4740-9440-ECD9894CCE03@.microsoft.com...
>
>|||Hi
This tends to imply that you transaction date is a confusing name! If these
can be back dates then you will potentially get different results each time
you run the query and it will not be historically static. If you don't have
sufficient auditing of the changes to your data it may be hard to prove why
you get different results.
Try something like:
SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
FROM [Inventory Transactions] IT
JOIN Products P ON P.ProductID=IT.ProductID
JOIN (
SELECT X.ProductID, X.StoreID, MAX(I.TransactionID) AS MAXTID
FROM [Inventory Transactions] I
JOIN (
SELECT Q.ProductID, T.StoreID, MAX(T.TransactionDate) AS MaxDate
FROM [Inventory Transactions] T
LEFT JOIN Products Q ON Q.ProductID=T.ProductID
WHERE T.TransactionDate<='03/02/2005 10:02:26'
AND StoreID IN(2)
GROUP BY Q.ProductID,T.StoreID) AS X ON I.ProductID=X.ProductID AND
I.StoreID=X.StoreID AND I.TransactionDate=X.MaxDate
) Y ON IT.ProductID=Y.ProductID AND IT.StoreID=Y.StoreID AND
IT.TransactionID=Y.MAXTID
ORDER BY P.ProductName
John
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:4149F671-75A3-4A82-95BD-841CF2FC55C4@.microsoft.com...
> Thanks for the help.
> We want it based on TransactionDate not the TransactionID. There could
> have
> been a transaction that had lower date but the TransactionID is the
> highest.
> This occurs, for example, when the user wants to post a transaction for
> previous month today. If the user post the previouse month transaction
> today,
> TransactionID is Max but the TransactionDate is not Max.
> We want a combination of both Max(TransactionDate) and Max(TransactionID)
> to
> be true, but here we see that the TransactionDate could not be unique and
> TransactionID is not correct by itself.
> --
> Mike
>
> "John Bell" wrote:
>|||Thank you John, it is working.
--
Mike
"John Bell" wrote:
> Hi
> This tends to imply that you transaction date is a confusing name! If thes
e
> can be back dates then you will potentially get different results each tim
e
> you run the query and it will not be historically static. If you don't hav
e
> sufficient auditing of the changes to your data it may be hard to prove wh
y
> you get different results.
> Try something like:
> SELECT IT.ProductID, IT.StoreID, IT.Quantity, P.ProductName
> FROM [Inventory Transactions] IT
> JOIN Products P ON P.ProductID=IT.ProductID
> JOIN (
> SELECT X.ProductID, X.StoreID, MAX(I.TransactionID) AS MAXTID
> FROM [Inventory Transactions] I
> JOIN (
> SELECT Q.ProductID, T.StoreID, MAX(T.TransactionDate) AS MaxDate
> FROM [Inventory Transactions] T
> LEFT JOIN Products Q ON Q.ProductID=T.ProductID
> WHERE T.TransactionDate<='03/02/2005 10:02:26'
> AND StoreID IN(2)
> GROUP BY Q.ProductID,T.StoreID) AS X ON I.ProductID=X.ProductID AND
> I.StoreID=X.StoreID AND I.TransactionDate=X.MaxDate
> ) Y ON IT.ProductID=Y.ProductID AND IT.StoreID=Y.StoreID AND
> IT.TransactionID=Y.MAXTID
> ORDER BY P.ProductName
> John
>
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:4149F671-75A3-4A82-95BD-841CF2FC55C4@.microsoft.com...
>
>|||Create a view that captures the max date
Create view VwMaxDate as
select ProductID, Max(Date) as MaxDate
group by ProductID
Then, you can join your table to the view and get the results you are
s
ing.
Select a.TransactionID, a.ProductID, a.StoreID, a.QTY, a.Date
from TableA a join VwMaxDate on a.ProductId = b.ProductId and a.Date =
b.MaxDate
