Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting distinct records Expand / Collapse
Author
Message
Posted Thursday, September 27, 2007 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 19, 2010 2:59 AM
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
Post #403383
Posted Thursday, September 27, 2007 7:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 42,443, Visits: 35,498
What do you want the result to look like?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #403474
Posted Thursday, September 27, 2007 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2008 11:53 AM
Points: 46, 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.
Post #403502
Posted Friday, September 28, 2007 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 19, 2010 2:59 AM
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

Post #403937
Posted Friday, September 28, 2007 8:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:30 AM
Points: 37, Visits: 342
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
Post #404278
Posted Friday, September 28, 2007 8:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:30 AM
Points: 37, Visits: 342
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]
Post #404286
Posted Friday, September 28, 2007 10:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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 ;).
Post #404303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse