Need SQL Server Help

Question

Need SQL Server Help

Harshen Parikh

Grasshopper

Points: 12
More actions
July 11, 2005 at 9:49 pm

#165170

Well I have creted the one table which fetches the data from two table. finally In my Temp Table i have the data like this

MediaAssetId             ArticleId
12788                      54933
12787                      54999
12785                      54855
12779                      54933
12746                      54999
12745                      54999
Well if you look at the data it duplicates with the Articleid. What actually i want to produce is something like this

MediaAssetID              ArticleId
12788                         54933
12787                         54999
12785                        54855
I have roughly 3000 rows and i have to discard the duplicates and pick up the top MediaAssetid for that particular Articleid.

I hope you can understand the question. If you need more info please let me know
Thanks in Advance
Harshen

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Wajih ul Hasan SSC Veteran Points: 265 More actions · Answer 1

i havent been in touch with SQL a lot lately but i do know that there are keywords UNIQUE and DISTINCT which do precisely this thing...i dont have a reference book with me right now else i wud have given an example...trying looking it up..i'll check back later too

Wajih ul Hasan SSC Veteran Points: 265 More actions · Answer 2

here's a template if this might help...

SELECT [ALL | DISTINCT] columnname1 [,columnname2]FROM tablename1 [,tablename2][WHERE condition] [ and|or condition...][GROUP BY column-list][HAVING "conditions][ORDER BY "column-list" [ASC | DESC] ]

Harshen Parikh Grasshopper Points: 12 More actions · Answer 3

Well I have tried with distinct but it won't give me the output as i wanted.It will select distinct ARticleid but since MediaAsset is also associated with it, It will produce the similar result.

Just wanted to get Latest MediaAsset for Every Article.

Any help will be appreciated.

thanks

Harshen

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

It should be something like this:

Select A.* from tblMediaAssets A

inner join

(select max(MediaAssetID) MediaAssetID from tblMediaAssets

Group By ArticleID) B

on

A.MediaAssetID= B.MediaAssetID

Order By A.MediaAssetID

Let me know if it works!

**ASCII stupid question, get a stupid ANSI !!!**

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 5

Assuming u have got the data in #tmpMedia table, simply executing this query will do it for you:

select distinct articleid,max(assetid) from #tmpMedia

group by articleid

order by 1

Regards,

Dilip

Harshen Parikh Grasshopper Points: 12 More actions · Answer 6

Well That's a Great Response and It really Works. However in My Table i have couple of more columns like title,Abstract associated with Every Row.

Just Image in My TempTable it is like this

MediaAssetId Articleid Title

12755 54933 "Sql Server Help is the best""Corby Failed to get Bailed"

12754 54933 "Petrol Prices are Increasing"

12752 5110 "Blah Blah"

12751 5110 "Corby Failed to get Bailed"

And What i want to produce is:

MediaAssetId Articleid Title

12755 54933 "Sql Server Help is the best""Corby Failed to get Bailed"

12752 5110 "Blah Blah"

Well i work in the news channel so i have to be very cautious regarding fetching data

Thanks For your all help

Harshen

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

Harshen - not sure if you're responding to Dilip or me - however, does my query not work with your table ?! I am selecting all fields and getting the max MediaAssetId per group...

what is not working ?!

**ASCII stupid question, get a stupid ANSI !!!**

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 8

I added one more col to my temp table and This will get u the title which you were looking for...

select distinct a.articleid,max(a.assetid) as mediaassetid

,(select distinct top 1 descript from #tmpMedia where articleid=a.articleid)

from #tmpMedia a

group by a.articleid

HTH

Regards,

Dilip

Harshen Parikh Grasshopper Points: 12 More actions · Answer 9

Fantastic Dilip and Sushila. Both of your queries works great for me.

infact Thanks dilip for responding so quickly. It solves my problem so far.

thanks so much

Feeling better

Harshen