By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

Display a roll having MINUMUM MARKS IN TWO SUBJECTS?

P: 95
create table student7(Roll int,English int,Hindi int,Maths int)
insert into student7 values(1,12,15,9)
insert into student7 values(2,23,3,13)
insert into student7 values(3,15,12,4)

select roll from student7 where
maths=(select min(maths) from student7)
AND
English=(select min(english)from student7)


Above Query is not returning any row.
Second thing, here we limited our search to two subjects but if we want to display a roll without declaring any particular subject..then
Apr 2 '08 #1
Share this Question
Share on Google+
14 Replies


code green
Expert 100+
P: 1,726
Query is not returning any row
Use code tags and not bold
Expand|Select|Wrap|Line Numbers
  1. select roll from student7 where 
  2. maths=(select min(maths) from student7) 
  3. AND
  4. English=(select min(english)from student7)
If you study the logic in this, you will see that it requires a student to get the lowest mark in both English and Maths.
Not even I achieved that.
The sub-query idea looks awful and should probably be a JOIN.
What result are you looking for?
.
Apr 2 '08 #2

P: 95
Tell me the Query, which retrieved a roll having minimum marks in english and maths..............
Apr 2 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Tell me the Query, which retrieved a roll having minimum marks in english and maths..............
What version of SQL are you using?

-- CK
Apr 2 '08 #4

ck9663
Expert 2.5K+
P: 2,878
Anyway..............

This will NOT work in SQL 2000. This query will give you the rolls with lowest grade in Math and English.

Expand|Select|Wrap|Line Numbers
  1. drop table student7
  2.  
  3. create table student7(Roll int,English int,Hindi int,Maths int)
  4.  
  5. insert into student7 values(1,12,15,9)
  6. insert into student7 values(2,23,3,13)
  7. insert into student7 values(3,15,12,4)
  8.  
  9. select * from 
  10. (select top 1 'English' as Subject, Roll, English from student7 order by English asc) E
  11. union 
  12. select * from
  13. (select top 1 'Maths' as Subject, Roll, English from student7 order by Maths asc) M
  14. drop table student7
  15.  
You might want to consider creating this query dynamically.

-- CK
Apr 2 '08 #5

Delerna
Expert 100+
P: 1,134
This post is for your clarification

this query
Expand|Select|Wrap|Line Numbers
  1. select roll from student7 where 
  2. maths=(select min(maths) from student7) 
  3. AND
  4. English=(select min(english)from student7)
  5.  
would work if you changed the AND to an OR. It would return one or two records. One if the same person got the lowest score in english and maths,
otherwise there would be two. The person who got the lowest in maths and the person who got the lowest in english.

I agree with CK on the subqueries in the where.
In certain cases it is very much a legitimit technique, but you must consider the size of the table because each of those subqueries will be executed over and over again. Once for each and every record in the table.
So If you had a 10 million record table, for example.
One call to your query would result in the subquery being executed 10 million times.
Since you have 2 subqueries that means 20 million times. As you can imagine that could take a long time to execute, as effectively the query is scanning through and operating on some 200 million records.
On smaller tables its not so bad but its still better to try and come up with another solution if you can.

By the way, the statement about how many times the subqueries would get executed is made disregarding indexes. Indexes can change the situation drastically, for the better.
Apr 3 '08 #6

P: 95
I m using the version 2005. Ck ur query is rerurning the desired result.
Delerna, ya if I changed the And to Or, the query is working fine.
Thx all of u. Actually this is written technical of patni, question is not clear in itself....dats y facing the prob in understanding........


One thing see below--

Below query Display roll having min marks in english
select top 1 'English' as Subject,Roll,English as 'Marks'
from student7 order by english asc

Below query Display roll having min marks in maths
select top 1 'Maths' as Subject,Roll,maths as 'Marks'
from student7 order by maths asc

If we just combine these two queries using the join,,why error is coming
(select top 1 'English' as Subject,Roll,English as 'Marks'
from student7 order by english asc)
Union
(select top 1 'Maths' as Subject,Roll,maths as 'Marks'
from student7 order by maths asc)



Its a write Query.............
Apr 3 '08 #7

Delerna
Expert 100+
P: 1,134
Try it like this

Expand|Select|Wrap|Line Numbers
  1. Select Subject ,Marks
  2. from
  3. (select top 1 English as Subject,Roll,English as Marks
  4. from student7 order by english asc) a
  5. Union
  6. Select Subject ,Marks
  7. from
  8. (select top 1 Maths as Subject,Roll,maths as Marks
  9. from student7 order by maths asc)b
  10.  
Or this may work
Expand|Select|Wrap|Line Numbers
  1. select top 1 English as Subject,Roll,English as Marks
  2. from student7 
  3. order by english asc
  4. Union
  5. select top 1 Maths as Subject,Roll,maths as Marks
  6. from student7 
  7. order by maths asc
  8.  
I'm just not sure about the 'order by' working like this, and I can't test it as I don't have access to SQLServer at the moment
Apr 3 '08 #8

ck9663
Expert 2.5K+
P: 2,878
I m using the version 2005. Ck ur query is rerurning the desired result.
Delerna, ya if I changed the And to Or, the query is working fine.
Thx all of u. Actually this is written technical of patni, question is not clear in itself....dats y facing the prob in understanding........


One thing see below--

Below query Display roll having min marks in english
select top 1 'English' as Subject,Roll,English as 'Marks'
from student7 order by english asc

Below query Display roll having min marks in maths
select top 1 'Maths' as Subject,Roll,maths as 'Marks'
from student7 order by maths asc

If we just combine these two queries using the join,,why error is coming
(select top 1 'English' as Subject,Roll,English as 'Marks'
from student7 order by english asc)
Union
(select top 1 'Maths' as Subject,Roll,maths as 'Marks'
from student7 order by maths asc)



Its a write Query.............
The reason that code is not working is because, when enclosed in a parenthesis, the entire table act some sort of a table-valued function or a view. So doing this:

Expand|Select|Wrap|Line Numbers
  1. (select top 1 'English' as Subject,Roll,English as 'Marks'
  2. from student7 order by english asc)
  3. Union
  4. (select top 1 'Maths' as Subject,Roll,maths as 'Marks'
  5. from student7 order by maths asc)
is similar to doing this:

Expand|Select|Wrap|Line Numbers
  1. student7
  2. union
  3. student7
That's not how you union nor select a table.

-- CK
Apr 3 '08 #9

P: 95
Thx,for the above Query, See the below one more related with UNION only

When combining queries with UNION,you may use only one ORDER BY Clause and it must occur after the final SELECT statement.

create table student1(roll int,marks int)insert into student1 values(1,20)
insert into student1 values(2,10)
insert into student1 values(3,30)
select * from student1

create table student3(roll int,marks int)
insert into student3 values(4,40)
insert into student3 values(5,60)
insert into student3 values(6,50)
select * from student3

select roll from student1
union
select roll from student3
order by marks


Query in bold is not working.
I want to just ask that if i want to display the roll nos in the order related to the ascending order of marks, using the UNION,its necessary to fetch the marks column also....& if I want to display only roll nos ,is there any solution...
Apr 3 '08 #10

ck9663
Expert 2.5K+
P: 2,878
Thx,for the above Query, See the below one more related with UNION only

When combining queries with UNION,you may use only one ORDER BY Clause and it must occur after the final SELECT statement.

create table student1(roll int,marks int)insert into student1 values(1,20)
insert into student1 values(2,10)
insert into student1 values(3,30)
select * from student1

create table student3(roll int,marks int)
insert into student3 values(4,40)
insert into student3 values(5,60)
insert into student3 values(6,50)
select * from student3

select roll from student1
union
select roll from student3
order by marks


Query in bold is not working.
I want to just ask that if i want to display the roll nos in the order related to the ascending order of marks, using the UNION,its necessary to fetch the marks column also....& if I want to display only roll nos ,is there any solution...
This "When combining queries with UNION,you may use only one ORDER BY Clause and it must occur after the final SELECT statement." means the query will unionize your data first then sort it. Since the unionize data does not inculde the column Marks, you can use it to sort it. The solution is to use subquery on whichever (student1 or student3) be sorted.

If you really want to, try this:

Expand|Select|Wrap|Line Numbers
  1. select roll from
  2. (select roll, marks from student1 
  3. union
  4. select rol,l marksl from student3) a
  5. order by marks
  6.  
-- CK
Apr 3 '08 #11

P: 95
Hey thx Ck,,Grt u all r experts......
Apr 3 '08 #12

ck9663
Expert 2.5K+
P: 2,878
I know you got already, but this "you can use it to sort it" actually means "you CAN NOT use it to sort it"

The phrase should actually say:


This "When combining queries with UNION,you may use only one ORDER BY Clause and it must occur after the final SELECT statement." means the query will unionize your data first then sort it. Since the unionize data does not inculde the column Marks, you CAN NOT use it to sort it. The solution is to use subquery on whichever (student1 or student3) be sorted.

Sorry, my bad...


-- CK
Apr 3 '08 #13

P: 95
ya ,Its write we cannot sort the rows by the column if it is not retrieved, But Ck,the answer in the previous scrap given by you is sorting the rows by the column marks, even if we do not fetching it...
Apr 4 '08 #14

ck9663
Expert 2.5K+
P: 2,878
Are you referring to this:

Expand|Select|Wrap|Line Numbers
  1. select roll from
  2. (select roll, marks from student1 
  3. union
  4. select rol,l marks from student3) a
  5. order by marks
If we're going to analyze this code, the union will be executed first. The entire view produced by the union have the marks column. So you can use it for sorting.

The above code is similar to this:

Expand|Select|Wrap|Line Numbers
  1. select roll from student1 
  2. order by marks
As you can see the Marks column is inside student1 table but not on the select list. So the in query that I suggested the column Marks is inside the view (as produced by your query) but not on the select list.

Too wordy? Sorry I hope I make sense.

-- CK
Apr 4 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.