Select distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select distinct

Author

Topic

roco
Starting Member

9 Posts

Posted - 2010-02-03 : 10:54:14

Hello,

I have a problem with how to get distinct rows(regarding tr.rid below). Have tried several ways but without success so far.

I would much appreciate any help on this!

With Recs AS
(
select ROW_NUMBER() OVER (order by x.ClickDate asc) as RowNumber,tr.rid,tr.del,tr.deldate,tr.CreDate,tr.creuid,tr.rentsrc,y.lurl,x.cdate from tr
inner join tm x on x.rid = tr.rid
and x.mid = 1
and x.lid = 2
inner join tli y on y.lid = x.lid
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 11:01:01

do you mean this?


SELECT rid,del,deldate,CreDate,creuid,rentsrc,lurl,cdate
FROM
( 
select ROW_NUMBER() OVER (PARTITION BY tr.rid order by x.ClickDate asc) as RowNumber,tr.rid,tr.del,tr.deldate,tr.CreDate,tr.creuid,tr.rentsrc,y.lurl,x.cdate from tr
inner join tm x on x.rid = tr.rid 
and x.mid = 1
and x.lid = 2
inner join tli y on y.lid = x.lid
)t
WHERE t.RowNumber=1

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 04:05:36

Thanks for helping me out! I did get unique rows but with a problem. What happens after the sql in my last post is this:

select * into #tempRecs from Recs where RowNumber Between 1 and 10

Now the row count doesn't work. I will try to fix this but if you have an idea I very much appreciate if you would share it with me.

Thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:08:31

quote:
Originally posted by roco

Thanks for helping me out! I did get unique rows but with a problem. What happens after the sql in my last post is this:

select * into #tempRecs from Recs where RowNumber Between 1 and 10

Now the row count doesn't work. I will try to fix this but if you have an idea I very much appreciate if you would share it with me.

Thanks a lot!

why RowNumber doesnt work? whats the problem you're facing?

Sachin.Nand

2937 Posts

Posted - 2010-02-04 : 04:11:47

quote:

Originally posted by visakh16

do you mean this?


I think u missed the Rownumber field in the derived table

SELECT RowNumber, rid,del,deldate,CreDate,creuid,rentsrc,lurl,cdate
FROM
( 
select ROW_NUMBER() OVER (PARTITION BY tr.rid order by x.ClickDate asc) as RowNumber,tr.rid,tr.del,tr.deldate,tr.CreDate,tr.creuid,tr.rentsrc,y.lurl,x.cdate from tr
inner join tm x on x.rid = tr.rid 
and x.mid = 1
and x.lid = 2
inner join tli y on y.lid = x.lid
)t
WHERE t.RowNumber=1

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:13:42

quote:

Originally posted by Idera

quote:

Originally posted by visakh16

do you mean this?


I think u missed the Rownumber field in the derived table

SELECT RowNumber, rid,del,deldate,CreDate,creuid,rentsrc,lurl,cdate
FROM
( 
select ROW_NUMBER() OVER (PARTITION BY tr.rid order by x.ClickDate asc) as RowNumber,tr.rid,tr.del,tr.deldate,tr.CreDate,tr.creuid,tr.rentsrc,y.lurl,x.cdate from tr
inner join tm x on x.rid = tr.rid 
and x.mid = 1
and x.lid = 2
inner join tli y on y.lid = x.lid
)t
WHERE t.RowNumber=1

PBUH

Nope I left it by purpose. I didnt feel need of rownumber in select as its used only for getting distinct values of the group

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 05:00:37

Hello,

I think my last post disappear?!

Anyway, the problem I have is that even though I use the max function on the sql below, I don't get unique rows, I get more than one row regarding the tr.rid. When I use the sql you helped me with I get unique rows regarding tr.rid but the row count isn't working as I need it to. I need the row count to increment for each row.

With Recs AS
(
select max(tr.rid) as rid,tr.del,max(tr.deldate) as deldate,max(tr.credate) as credate,
max(tr.cbui) as cbui,max(tr.res) as res,max(y.lu) as lu,max(x.cdate) as cdate
from tr
inner join tx x on x.rid = tr.rid
and x.mmqid = 1
and x.liid = 2
inner join ty y on y.liid = x.liid
group by x.cdate,tr.rid,tr.del
)
select * into #tempRec from Recs where RowNumber Between 1 and 10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:02:37

is this what you're after?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139339

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources