Removing duplicated rows in VIEW

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

General SQL Server Forums

New to SQL Server Programming

Removing duplicated rows in VIEW

Author

Topic

torero13
Starting Member

7 Posts

Posted - 2010-01-19 : 15:15:14

Hello All:
I have a table that comes from another system that contains:
Item
Cost Price
Sales Price
Price Date
Every time that a price changes, it creates a new row that can change both or just one of the prices and off course the “price date”.
I want to create a view that brings only the row that contains the newest “Price Date” for each item.
Can somebody help me on that?
Thanks,

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-19 : 15:29:07

Create the sql statement , that would give you the result you require , such as
SELECT Item,[Cost Price],[Sales Price],[Price Date] form myTable order by [Price Date] DESC

and then place this in view such as

CREATE VIEW Test AS
SELECT Item,[Cost Price],[Sales Price],[Price Date] from myTable order by [Price Date] DESC

You could then reference as :

SELECT * FROM Test

Jack Vamvas
--------------------
http://www.ITjobfeed.com

torero13
Starting Member

7 Posts

Posted - 2010-01-19 : 15:40:20

Sorry for my ignorance:
I have the view created:
SELECT ITEM, DASH, [LP DATE], [STD COST], [LIST PRICE]
FROM dbo.NDSEPPRICE
It brings all records and it duplicates some of them because the price changed in 2010. Can I use any statement that will bring only the Newest?

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-19 : 15:55:49

See if this works...

CREATE VIEW Test AS
SELECT A.Item,A.[Cost Price],A.[Sales Price],A.[Price Date] from myTable A
inner join (select Item, max([Price Date]) as [Price Date] from myTable group by Item) B
on A.Item = B.Item and A.[Price Date] = B.[Price Date]

-Shan

torero13
Starting Member

7 Posts

Posted - 2010-01-19 : 16:14:00

Sorry But did not understand it. Table A and Table B?
The only table that I have is NDSEPPRICE.

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-19 : 16:20:25

They are not Table A and Table B they are Aliase,

CREATE VIEW Test AS
SELECT A.Item,A.[Cost Price],A.[Sales Price],A.[Price Date] from NDSEPPRICE A
inner join (select Item, max([Price Date]) as [Price Date] from NDSEPPRICE group by Item) B
on A.Item = B.Item and A.[Price Date] = B.[Price Date]

Then try select * from Test

You got to read some basic T-SQL.

-Shan

torero13
Starting Member

7 Posts

Posted - 2010-01-19 : 16:30:44

Dear Shan:

With all the respect. I love you!!!!
It works perfectly and I know that I need to read some basics, trying to get the time to do it.

THANK YOU!!!!

shan
Yak Posting Veteran

84 Posts

Posted - 2010-01-19 : 16:39:38

You are welcome

-Shan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources