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
+ 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
14 Replies

 Expert 100+ P: 1,726 Query is not returning any row Use code tags and not bold Expand|Select|Wrap|Line Numbers 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? . 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

 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

 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 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 Apr 2 '08 #5

 Expert 100+ P: 1,134 This post is for your clarification this query Expand|Select|Wrap|Line Numbers 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. 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

 Expert 100+ P: 1,134 Try it like this Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers 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 Apr 3 '08 #8

 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 (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: Expand|Select|Wrap|Line Numbers student7 union 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

 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 select roll from (select roll, marks from student1  union select rol,l marksl from student3) a order by marks   -- CK Apr 3 '08 #11

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

 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

 Expert 2.5K+ P: 2,878 Are you referring to this: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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 Apr 4 '08 #15

### Post your reply

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