how to avoid duplictae records of column in a table in a select query in this proc?

  • here iam having two table @companytable and @boardtable in the @boardtable table will have foreign key of companyid from @companytable and i just want to avoid duplicate record of company name repeated

    DECLARE @companytable table

    (

    companyid int,

    companyname varchar(200),

    countrycode varchar(3),

    city varchar(30),

    isactive int

    )

    insert into @companytable

    select 1,'s1','US','California',1 union all

    select 2,'s2','in','Chennai',1 union all

    select 3,'s1','in','Chennai',1 union all

    select 4,'s6','in','bangolre',1 union all

    select 5,'s5','uk','london',1 union all

    select 6,'s1','uk','london',1 union all

    select 7,'s5','us','newyork',1

    DECLARE @boardtable table

    (

    boardid int identity(1,1),

    companyid int ,

    createddate datetime,

    isactive int

    )

    insert into @boardtable

    select 1,'2012-11-01 12:34:49.860',1 union all

    select 2,'2012-11-02 12:34:49.860',1 union all

    select 3,'2012-11-04 12:34:49.860',1 union all

    select 4,'2012-11-06 12:34:49.858',1 union all

    select 5,'2012-11-09 11:34:49.859',1 union all

    select 6,'2012-11-09 11:34:49.860',1 union all

    select 7,'2012-11-09 12:34:49.861',1

    here just trying a select proc by joining this two table and select top 4 oder by the created date desc

    select top 4 r.boardid,r.companyid,c.companyname,c.countrycode,c.city

    from

    @companytable c

    join

    @boardtable r

    on c.companyid=r.companyid

    order by createddate desc

    but it provinding out put like this

    boardid companyid companyname countrycode city

    7 7 s5 us newyork

    5 5 s5 uk london

    6 6 s1 uk london

    4 4 s6 in bangolre

    but iam trying for the out putlike this

    boardid companyid companyname countrycode city

    7 7 s5 us newyork

    6 6 s1 uk london

    4 4 s6 in bangolre

    2 2 s2 in Chennai

  • You can do it as follows:

    --Query for your requirement

    Select boardid, companyid, companyname, countrycode, city From

    (

    Select b.boardid, b.companyid, a.companyname, a.countrycode, a.city ,ROW_NUMBER() Over (Partition By a.CompanyName Order by b.createddate DESC) As rn

    From @companytable as a

    JOIN @boardtable as b On a.companyid = b.companyid

    ) As p

    Where rn = 1

    Order by boardid DESC

    The query gets you the data that you want. But, you did not mention the logic which was behind your requirement. The query may need some change if your logic is different from what i used.

    From next time please post the logic behind the requirement as well so that it becomes easier to deduce a solution.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • hi

    vinu512

    thanks for the solution which u just posted for me

  • As company S1 has offices in UK,US and IN. how is SQL supposed to know that you want the US office instead of one of the others.

    Unless you specify an ORDER BY clause, you cannot guarantee that the data will always be returned in the same sequence so the results of the query may change even if the data does not.

  • sivajii (11/15/2012)


    hi

    vinu512

    thanks for the solution which u just posted for me

    You're Welcome. Glad it was halpfull for you.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 5 posts - 1 through 4 (of 4 total)

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