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

Distinct

Author

Topic

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 04:45:02

Hello again,

I forgot to ask about this. What I don't understand is why I don't get unique rows from this sql even though I use the max function.

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

With this sql I get more than one row regarding the tr.rid when I only want unique.

Hope you can help me out.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:46:28

it just gives you maximum values from other columns for each group of x.cdate,tr.rid,tr.del value. the values returned wont necessarily be the ones in the same row of cte

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 04:50:21

do you need this?


select columns...
from
(
select row_number() over (partition by tr.rid order by tr.credate desc) as seq,
tr.rid as rid,tr.del,tr.deldate as deldate,tr.credate as credate,
tr.cbui as cbui,tr.res as res,y.lu as lu,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 
)t
where seq=1

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 05:14:53

What I need is what you helped me with, to get unique rows on tr.rid but also with rownumber incrementing for each row I get so that I can use it for paging purpose. Do you know what I mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:20:05

quote:
Originally posted by roco

What I need is what you helped me with, to get unique rows on tr.rid but also with rownumber incrementing for each row I get so that I can use it for paging purpose. Do you know what I mean?

ok the rownumber should be based on what order? order of trid?

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 05:22:42

So I would need the sql below to return unique rows regarding tr.rid and an incremented rownumber for each row.

Hope I am being clear.

select columns...
from
(
select row_number() over (partition by tr.rid order by x.cdate desc) as seq,
tr.rid as rid,tr.del,tr.deldate as deldate,tr.credate as credate,
tr.cbui as cbui,tr.res as res,y.lu as lu,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
)t
where seq=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:24:09

anyways here you go:-


select rid,del,deldate,credate,
cbui,res,lu,cdate
from
(
select row_number() over (order by <your ordering field here>) as rn,rid,del,deldate,credate,
cbui,res,lu,cdate
from
(
select row_number() over (partition by tr.rid order by tr.credate desc) as seq,
tr.rid as rid,tr.del,tr.deldate as deldate,tr.credate as credate,
tr.cbui as cbui,tr.res as res,y.lu as lu,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 
)t
where seq=1
)r
where rn between @start and @end

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 05:30:45

I guess the rownumber shuld be based on the tr.rid since these should be unique.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

quote:
Originally posted by roco

I guess the rownumber shuld be based on the tr.rid since these should be unique.

then fill in in place holder in last suggestion and see

roco
Starting Member

9 Posts

Posted - 2010-02-04 : 05:46:14

I think I just missed your last post. I have tried your last post out and I think it works fine! Thanks a lot for helping me out! Hopefully I will get this to work in the context where I need to use it and have no more questions regarding this specific topic:) Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 05:52:08

you're welcome

no worries. let us know if you face any more issues

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources