Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting distinct records


Selecting distinct records

Author
Message
deepakdilse
deepakdilse
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 43
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 aaa AAA

2 1 aaa BBB

3 1 aaa CCC

4 2 bbb DDD

5 2 bbb EEE



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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71526 Visits: 44958
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


Janet Keith-489067
Janet Keith-489067
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 85
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.
deepakdilse
deepakdilse
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 43
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
jon-474332
jon-474332
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 343
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 Wink,

Jon
jon-474332
jon-474332
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 343
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 Wink,



Jon

[/quote]
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25155 Visits: 9671
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 Wink.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search