September 27, 2007 at 3:05 am
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
September 27, 2007 at 7:20 am
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
September 27, 2007 at 8:15 am
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.
September 28, 2007 at 3:20 am
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
September 28, 2007 at 8:06 pm
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
September 28, 2007 at 8:21 pm
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]
September 28, 2007 at 10:02 pm
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