Removing non identical duplicates from a multi-table query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Removing non identical duplicates from a multi-table query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-14 : 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

992 Posts

Posted - 2003-07-14 : 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 - 2003-07-14 : 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-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources