How to write Select Query for this

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)

How to write Select Query for this

Author

Topic

anuradhay
Starting Member

41 Posts

Posted - 2005-04-01 : 00:14:36

I have a table like the one given below

StudentID Mark1 Mark2 Mark3 Mark4
001 52 65 45 46
002 30 20 25 30
003 75 69 80 95

Now i want to retrieve only those students who has got more than 50 in two subjects..

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-01 : 00:35:02

Since Mark1, Mark2 etc are columns and not in another table, I can't see any easy way to do it other than

select * from Marks 
where (Mark1 > 50 and Mark2 > 50)
OR (Mark1 > 50 and MArk3 > 50)
OR (Mark1 > 50 and Mark4 > 50)
OR (Mark2 >50 and Mark3 >50)
OR (Mark2 > 50 and Mark4 > 50)
OR (Mark3 > 50 and Mark4 > 50)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-01 : 00:47:41

[code]select
StudentID,
Mark1,
Mark2,
Mark3,
Mark4
from
Marks
where
(case when Mark1 > 50 then 1 else 0 end +
case when Mark2 > 50 then 1 else 0 end +
case when Mark3 > 50 then 1 else 0 end +
case when Mark4 > 50 then 1 else 0 end ) = 2
[/code]

CODO ERGO SUM

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-01 : 00:48:54

yep, as I said, I can't see any easy way to do it...

and just to be picky - Mike, you'd need to make it >=2 rather than just =2
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-04-01 : 02:23:17

Jones,

Your solution is nice.

Rafiq
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"You have to dream before your dreams can come true" -- President Of India

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-01 : 02:26:42

quote:
Jones,
Your solution is nice.

Mr Jones' solutions are always nice....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-01 : 08:31:05

Well, I gave it to him the way he asked, "only those students who has got more than 50 in two subjects". Now if he had said "two or more"...

quote:
Originally posted by rrb

yep, as I said, I can't see any easy way to do it...

and just to be picky - Mike, you'd need to make it >=2 rather than just =2
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

CODO ERGO SUM

ts_abbott@hotmail.com
Starting Member

36 Posts

Posted - 2005-04-01 : 10:21:16

here's an alternative solution which may be more extensible...


SELECT a.StudentId, COUNT(*) AS NumMarksAboveFifty FROM
	(SELECT StudentId, Mark1 AS Mark FROM Marks UNION ALL
	SELECT StudentId, Mark2 AS Mark FROM Test UNION ALL
	SELECT StudentId, Mark3 AS Mark FROM Test UNION ALL
	SELECT StudentId, Mark4 AS Mark FROM Test) a
WHERE a.Mark > 50
GROUP BY StudentId
HAVING COUNT(*) > 1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-01 : 11:35:58

I doubt that scanning the table four times, prerforming a union all, and then grouping the output would be more extensible that a single scan of the table.

If you do want to do it that way though, at least move the filter into the union all selects to reduce the size of the result set to group.

SELECT
	a.StudentId,
	COUNT(*) AS NumMarksAboveFifty
FROM
	(
	SELECT StudentId FROM Marks where Mark1 > 50 UNION ALL
	SELECT StudentId FROM Marks where Mark2 > 50 UNION ALL
	SELECT StudentId FROM Marks where Mark3 > 50 UNION ALL
	SELECT StudentId FROM Marks where Mark4 > 50
	) a
GROUP BY
	StudentId
HAVING
	COUNT(*) > 1

quote:

Originally posted by ts_abbott@hotmail.com

here's an alternative solution which may be more extensible...


SELECT a.StudentId, COUNT(*) AS NumMarksAboveFifty FROM
	(SELECT StudentId, Mark1 AS Mark FROM Marks UNION ALL
	SELECT StudentId, Mark2 AS Mark FROM Test UNION ALL
	SELECT StudentId, Mark3 AS Mark FROM Test UNION ALL
	SELECT StudentId, Mark4 AS Mark FROM Test) a
WHERE a.Mark > 50
GROUP BY StudentId
HAVING COUNT(*) > 1

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 11:42:19

[code]SELECT
StudentID
,Mark1
,Mark2
,Mark3
,Mark4
FROM
Marks
WHERE
1 > SIGN(Mark1/51) + SIGN(Mark2/51) + SIGN(Mark3/51) + SIGN(Mark4/51)[/code]

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-01 : 11:52:52

Shouldn't that be?

WHERE
	1 < SIGN(Mark1/51) + SIGN(Mark2/51) + SIGN(Mark3/51) + SIGN(Mark4/51)

quote:

Originally posted by rockmoose

SELECT
	StudentID
	,Mark1
	,Mark2
	,Mark3
	,Mark4
FROM
	Marks
WHERE
	1 > SIGN(Mark1/51) + SIGN(Mark2/51) + SIGN(Mark3/51) + SIGN(Mark4/51)

rockmoose

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 11:59:51

Big difference in logic.
Thanks Michael.

It's funny how the smallest of things can cause so much aggravation!
grrrrrrrrrrrrrrrrrr...

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-01 : 12:06:46

I guess you are also assuming they can't have something like -73 for a test score where the SIGN function would return -1.

Of couse, that could never happen, because "It's handled by the front-end".

quote:
Originally posted by rockmoose

Big difference in logic.
Thanks Michael.

It's funny how the smallest of things can cause so much aggravation!
grrrrrrrrrrrrrrrrrr...

rockmoose

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 12:17:24

Are You bugging me Michael ?