| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 07/14/2003 : 07:23:14
|
Richard writes "I have the following query, which returns values for every row in the tranasction table, but how do I modify it to remove the duplicated rows for a Stock_ID with more than one transaction and just return all rows from the stock table with only the last transaction for each Stock_ID.
SELECT TOP 100 PERCENT dbo.Stock.Stock_ID,
dbo.Stock.Location,
dbo.Stock.Quantity,
dbo.Stock.Unique_ID,
dbo.Stock.Deleted,
dbo.Parts.Part_Description,
dbo.Parts.Part_Number,
dbo.Parts.Machine_Type,
dbo.Parts.Internal_Price,
dbo.[Transaction].Document_No,
dbo.[Transaction].Document_Date,
dbo.Owner.Owner_Code,
dbo.Supplier.Supplier_Code
FROM dbo.Supplier
INNER JOIN dbo.[Transaction] ON dbo.Supplier.Supplier_ID = dbo.[Transaction].Supplier_ID
INNER JOIN dbo.Stock
INNER JOIN dbo.Parts ON dbo.Stock.Part_ID = dbo.Parts.Part_ID
INNER JOIN dbo.Owner ON dbo.Stock.Owner_ID = dbo.Owner.Owner_ID ON dbo.[Transaction].Stock_ID = dbo.Stock.Stock_ID
ORDER BY dbo.Stock.Stock_ID
Here are the related tables
[Owner]
[Owner_ID] [int] IDENTITY (1, 1) NOT NULL
[Owner_Name] [varchar] (100) NULL
[Owner_Code] [varchar] (10) NULL
[Parts]
[Part_ID] [int] IDENTITY (1, 1) NOT NULL
[Part_Description] [varchar] (50) NULL
[Part_Number] [varchar] (50) NULL
[Machine_Type] [varchar] (50) NULL
[Internal_Price] [money] NULL
[Stock]
[Stock_ID] [int] IDENTITY (1, 1) NOT NULL
[Part_ID] [int] NULL
[Owner_ID] [int] NULL
[Location] [nvarchar] (100) NULL
[Quantity] [int] NULL
[Unique_ID] [nvarchar] (32) NULL
[Deleted] [bit] NULL
[Supplier]
[Supplier_ID] [int] IDENTITY (1, 1) NOT NULL
[Supplier_Name] [nvarchar] (100) NULL
[Supplier_Code] [varchar] (10) NULL
[Address] [varchar] (50) NULL
[Transaction]
[Transaction_ID] [int] IDENTITY (1, 1) NOT NULL
[Stock_ID] [int] NULL
[Stock_IN_Quantity] [int] NULL
[Stock_OUT_Quantity] [int] NULL
[Document_No] [varchar] (50) NULL
[Customer_ID] [int] NULL
[Supplier_ID] [int] NULL
[Document_Date] [datetime] NULL" |
|
|
setbasedisthetruepath
Used SQL Salesman
USA
992 Posts |
Posted - 07/14/2003 : 09:00:11
|
quote:
modify it to remove the duplicated rows for a Stock_ID with more than one transaction and just return all rows from the stock table with only the last transaction for each Stock_ID
Bear in mind that in SQL, there is no "last" - no concept of physical row order. There is certainly a latest, or the largest, but not a last.
If you want the latest transaction for a given stock, which I'll take to be the document_date column, you would ask for:
select ...
from stock s
inner join transaction t on s.stock_id = t.stock_id
where not exists (
select 1
from transaction
where stock_id = t.stock_id and document_date > t.document_date)
Jonathan
{0} |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/14/2003 : 09:55:14
|
I was wondering what a non identical duplicate meant...
If documnet date means to you the "last", like Jonathan mentioned, what he posted will is good to go...
If not you need to come up with the definition of "last"
Brett
8-) |
|
|
| |
Topic |
|
|
|
Removing non identical duplicates from a multi-table query
Forum Locked
