Selecting distinct records

  • Hi There,

    i want to select distinct records form the DB and the column on which i will be appling distinct will come from the client application. to make things clear ,here i am giving some samples which depicts my problem.

    create table T1 (id int primary key, name nvarchar(50)) --this is my first table

    create table T2 (id int primary key, fk int references T1(id), name nvarchar(50)) -- Second table

    --inserting some records

    insert T1 (id, name) values (1,'aaa')

    insert T1 (id, name) values (2,'bbb')

    insert T1 (id, name) values (3,'ccc')

    insert T1 (id, name) values (4,'ddd')

    insert T1 (id, name) values (5,'eee')

    ----inserting some records

    insert T2 (id, fk,name) values (1, 1, 'AAA')

    insert T2 (id, fk,name) values (2, 1, 'BBB')

    insert T2 (id, fk,name) values (3, 1, 'CCC')

    insert T2 (id, fk,name) values (4, 2, 'DDD')

    insert T2 (id, fk,name) values (5, 2, 'EEE')

    --creating a view out of two tabls

    create view vT1T2

    as

    select T1.id, T1.name as nameA,T2.name as nameB

    from T1 join T2 on T1.id = T2.fk

    --now i am using CTE in my query to get unique records ,say i want records with unique ID

    with DistinctSelect as (

    select distinct id, nameA from vT1T2 --here i am selecting Id and NameA,both are from table T1

    ),

    SelectResult AS (

    select row_number() over(order by id) as rowno, id, nameA

    from DistinctSelect

    )

    select * from SelectResult --here the result is fine ,i will get the distinct IDs

    --------------------------the results

    rowno ID nameA

    1 1 aaa

    2 2 bbb

    now if i want a column from other table(T2) ,to be added into the resultset of the query,

    the query looks something like this

    with DistinctSelect as (

    select distinct id, nameA,nameB from vT1T2 --here i adding nameB ,which is a column in T2

    ),

    SelectResult AS (

    select row_number() over(order by id) as rowno, id, nameA,nameB --nameB added

    from DistinctSelect

    )

    select * from SelectResult

    now i will get duplicate Id in the result set ,the results are something like this

    rowno ID nameA nameB

    1 1 aaaAAA

    2 1 aaaBBB

    3 1 aaaCCC

    4 2 bbbDDD

    5 2 bbbEEE

    i understand that since i have a distinct on all the column ,i am geting duplicate ID's but the whole row is unique.

    my requirement is i want to distinctly select the ID column ,without compromising on the result set .

    hope i am clear ,could any body throw some light on it on how to slove the problem or go around it.

    Thanks in Advance

    Deepak

  • What do you want the result to look like?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The results you are getting appear correct in a one-many relationship.

    If this isn't what you need, I'd review the business requirments and the database design.

  • hi guys,

    thanks for your reply.

    On Sep 28, 8:29 am, deepak

    my requirement is i should getdistinctID ,i

    do not care about the other fields ,they may be decided by the where

    condition.let me make it more clear,

    lets say T1 has these data:

    ID name

    1 aaa

    2 bbb

    3 ccc

    4 ddd

    5 eee

    and T2 has

    ID FK name addr

    1 1 AAA CA

    2 1 AAA LA

    3 1 CCC CA

    4 2 DDD Ca

    5 2 EEE vegas

    if i have a where clause with T2.name=AAA,the whole query is something

    like this.

    with DistinctSelect as (

    selectdistinctid ,nameA,nameB,addr from vT1T2 --here i adding

    nameB ,which is a column in T2

    where nameB='AAA'

    ),

    SelectResult AS (

    select row_number() over(order by id) as rowno, id, nameA,nameB,addr

    -- nameB added

    from DistinctSelect

    )

    select * from SelectResult

    the results for the following quey will be

    rowno id nameA nameB addr

    1 1 aaa AAA CA

    2 1 aaa AAA LA

    so here id is notdistinct ,i am expecting my results to be somthing

    like this

    rowno id nameA nameB addr

    1 1 aaa AAA CA

    or

    rowno id nameA nameB addr

    1 1 aaa AAA LA

    hope i am clear , can i by any chance groupdistinctcolumns something

    like selectdistinct(id), nameA,nameB,addr from vt1t2.if i can some

    how achive grouping ofdistinct,then i think the problem is almost

    sloved.

    is there any way to work around it, could you please suggest.

    Thanks in Advance

    Deepak

  • Hi there,

    hmmm, sounds to me that you only needed 1 record to be return?? Correct me if i'm wrong, in SQL Server there is nothing such as "groupdistinctcolumns", but there are workarounds to this, one way i suggest is to use temp table to temporary store grouped data (might not yet be distinct) and then query the temp table with a where cause + DISTINCT keyword.

    For your case, Y not use TOP??

    SELECT TOP 1

    T1.[id], T1.[name] AS nameA, T2.[name] AS nameB, T2.[addr] FROM T1 INNER JOIN T2 ON T1.ID = T2.FK WHERE T2.[name]='AAA'

    then only 1 record will be returned.

    Cheers ;),

    Jon

  • by the way, if you would like to have

    1 1 aaa AAA CA

    2 1 aaa AAA LA

    being returned as

    1 1 aaa AAA CA/LA

    you can acheive it by using 2 queries. 1st select the address concat them into a variable, then attach the variable to your second query.

    if you need any example do let me know.

    Cheers ;),

    Jon

    [/quote]

  • Ya, but if you want to be able to do that no matter how many rows you want to concat, for any number of master jey, let ME know ;).

Viewing 7 posts - 1 through 6 (of 6 total)

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