Join Problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Join Problem

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-07-07 : 06:52:48

Hi
I have a table T1 having two column "name","Marks" given below:-
Name Marks
---------------
a 10
b 15
c 25
d 5
e 20
f 7
-----------

and I want Output Like This

Name NoOf_Name_Get_moreMarks NoOf_Name_Get_LessMarks
------------------------------------------------------------
a 3 2
b 2 3
c 0 5
d 5 0
e 1 4
f 4 1

I want name and nomber of name which have got more morks and less marks corresponding to each name.For Example First row of output means
a 3 2 means

There are 3 name(b,c,e)have more marks than "a".And
There are 2 name(d,f) have less marks than "a".

Ranjeet Kumar Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-07 : 07:15:21

[code]DECLARE @TABLE TABLE
(
[Name] CHAR(1),
Marks int
)
INSERT INTO @TABLE
SELECT 'a', 10 UNION ALL
SELECT 'b', 15 UNION ALL
SELECT 'c', 25 UNION ALL
SELECT 'd', 5 UNION ALL
SELECT 'e', 20 UNION ALL
SELECT 'f', 7

SELECT m.[Name],
NoOf_Name_Get_moreMarks = SUM(CASE WHEN o.Marks > m.Marks THEN 1 ELSE 0 END),
NoOf_Name_Get_LessMarks = SUM(CASE WHEN o.Marks < m.Marks THEN 1 ELSE 0 END)
FROM @TABLE m INNER JOIN @TABLE o
ON m.[Name] <> o.[Name]
GROUP BY m.[Name]

/*
Name NoOf_Name_Get_moreMarks NoOf_Name_Get_LessMarks
---- ----------------------- -----------------------
a 3 2
b 2 3
c 0 5
d 5 0
e 1 4
f 4 1
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-07-07 : 07:19:48

Thanks My problem have solved

Ranjeet Kumar Singh

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-22 : 14:52:24

declare @t table (Name varchar(50), Marks int)

insert @t
select 'a', 10
union all select 'b', 15
union all select 'c', 25
union all select 'd', 5
union all select 'e', 20
union all select 'f', 7

Another way:

select a.*, (select count(*) from @t b where b.marks > a.marks) as NoOf_Name_Get_moreMarks ,
(select count(*) from @t b where b.marks < a.marks) as NoOf_Name_Get_LessMarks
from @t a

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 01:57:31

Yes, that is another way to go.
But if you are concerned about performance (or having a multiuser environment) you should go for the suggestion made by khtan.

Compare execution plans and see which query that uses less resources.
Khtans suggestion uses 21 reads and johnsql suggestion needs 39 reads.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources