Post reply

Need SQL Server Help

  • 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

     

  • July 12, 2005 at 2:36 am

    #573267

    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

  • July 12, 2005 at 2:44 am

    #573273 

    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] ]

  • July 12, 2005 at 5:37 pm

    #573714

    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

     

     

  • July 12, 2005 at 6:13 pm

    #573720

    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 !!!**

  • July 12, 2005 at 11:15 pm

    #573760

    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

  • July 13, 2005 at 5:54 pm

    #574209

    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

  • July 13, 2005 at 6:23 pm

    #574212

    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 !!!**

  • July 13, 2005 at 9:58 pm

    #574247

    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

  • July 13, 2005 at 11:13 pm

    #574255

    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

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

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