Small Query

Question

Small Query

Chinna

SSCertifiable

Points: 5408
More actions
December 16, 2008 at 7:25 pm

#125095

create Table #temp1
(
id int,
Rno int,
sno int
)
insert into #temp1 values(10,125,32)
insert into #temp1 values(20,145,32)
insert into #temp1 values(10,125,32)
insert into #temp1 values(30,125,32)
insert into #temp1 values(10,20,32)
insert into #temp1 values(40,200,32)
insert into #temp1 values(50,120,32)
insert into #temp1 values(40,210,32)
insert into #temp1 values(30,320,32)
insert into #temp1 values(20,145,32)
insert into #temp1 values(20,145,32)
insert into #temp1 values(10,125,32)
insert into #temp1 values(10,215,32)
insert into #temp1 values(10,125,32)
basically i need is same id but different Rnos..how can i get that in a single Query...
here i want all the ids which has multiple RNOs but the RNOs shoule be distinct
i mean
id -10 has 125 4 times,215- 1 time,20 -1 time
now i should get all the ids which have more than one Rnos......but the Rnos should be different
in case of id-20 --it has 145 3 times but it has only one distinct Rno so i shouldnt get that if id(20) has Rno other than 145 i need to get that id also....can i know how can i write the Query to get everything in single shot
Thanks in Advance...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Instead of trying to describe in words what the result set should be, show us what the result set should be. You don't need to know how to write the query to create the expected result set.

I'm a visual type person, and seeing what you want would really help me and perhaps others as well.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Here's a start:

Select

id, Rno, Count(*)

From #temp1

Group By id, Rno

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Chinna SSCertifiable Points: 5408 More actions · Answer 3

barry by Executing that Query you will get like

10 125 4

10 20 1

but what all i need is how many ids have differenet RNos...so it should show like

10 2

so i belive i should do this am i correct

select id,count(distinct Rno) from #temp1

group by id

having count(distinct Rno)>1..

please do let me know if i am wrong

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'm sorry, but I'm confused as to what you want as a result set based on the sample data. Your verbal discription is confusing, and your answer to Barry is even more so. Can you clarify the output as to be 100% clear as to what is expected based on the sample data?

Including column headers for the output would also help.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Chris (12/16/2008)
select id,count(distinct Rno) from #temp1
group by id
having count(distinct Rno)>1

Looks good to me.

By the way, thanks for taking the time to provide the table definition and sample data, it really helps us to help you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Chinna SSCertifiable Points: 5408 More actions · Answer 6

Hi Lynn..

Actually i am putting verbal bcoz i need to find out how many ids have different Rnos..

anyway...i need the output this way..

id total

10 3

20 1

30 1

40 2

50 1

the total is count of different Rnos for same id....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Now that makes total sense. Here is some code:

create Table #temp1

(

id int,

Rno int,

sno int

)

insert into #temp1 values(10,125,32)

insert into #temp1 values(20,145,32)

insert into #temp1 values(10,125,32)

insert into #temp1 values(30,125,32)

insert into #temp1 values(10,20,32)

insert into #temp1 values(40,200,32)

insert into #temp1 values(50,120,32)

insert into #temp1 values(40,210,32)

insert into #temp1 values(30,320,32)

insert into #temp1 values(20,145,32)

insert into #temp1 values(10,125,32)

insert into #temp1 values(10,215,32)

insert into #temp1 values(10,125,32)

select

id,

count(distinct Rno)

from

#temp1

group by

id

order by

id;

drop table #temp1;

Tell me if that works for you.

Chinna SSCertifiable Points: 5408 More actions · Answer 8

Thanks Lynn..I shall test it tomm in my office and i shall let u know...

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman