Display a roll having MINUMUM MARKS IN TWO SUBJECTS?

[sonia.sardana]

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

[code green]

Query is not returning any row

Use code tags and not bold

select roll from student7 where 
maths=(select min(maths) from student7) 
AND
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?
.

[sonia.sardana]

Tell me the Query, which retrieved a roll having minimum marks in english and maths..............

[ck9663]

Tell me the Query, which retrieved a roll having minimum marks in english and maths..............

What version of SQL are you using?

-- CK

[ck9663]

Anyway..............

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

drop table student7
 
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 * from 
(select top 1 'English' as Subject, Roll, English from student7 order by English asc) E
union 
select * from
(select top 1 'Maths' as Subject, Roll, English from student7 order by Maths asc) M
drop table student7
 

You might want to consider creating this query dynamically.

-- CK

[Delerna]

This post is for your clarification

this query

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

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.

[sonia.sardana]

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.............

[Delerna]

Try it like this

Select Subject ,Marks
from
(select top 1 English as Subject,Roll,English as Marks
from student7 order by english asc) a
Union
Select Subject ,Marks
from
(select top 1 Maths as Subject,Roll,maths as Marks
from student7 order by maths asc)b
 

Or this may work

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
 

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

[ck9663]

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:

(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)

is similar to doing this:

student7
union
student7

That's not how you union nor select a table.

-- CK

[sonia.sardana]

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...

[ck9663]

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:

select roll from
(select roll, marks from student1 
union
select rol,l marksl from student3) a
order by marks
 

-- CK

[sonia.sardana]

Hey thx Ck,,Grt u all r experts......

[ck9663]

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

[sonia.sardana]

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...

[ck9663]

Are you referring to this:

select roll from
(select roll, marks from student1 
union
select rol,l marks from student3) a
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:

select roll from student1 
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