Home » SQL & PL/SQL » SQL & PL/SQL » min marks from subjects
min marks from subjects [message #215777] Tue, 23 January 2007 21:28 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello team

sir, i want to get the name of students who got minimum marks in atleast two subjects.

i had done a query which provides me minimum marks of subjects but i unable to proceed that query, can u please help me .
my table -

sql>select * from marksheet;

 rno name     subject      mark
---- ----    ---------   --------
 2   snil     java        60
 3   sudip    linux       80
 4   ashish   linux       50
 5   snil     linux       40
 7   sudip    java        60
 9   shailesh java        85
 10  shailesh linux       62
 12  ashish   pl          80
 13  sudip    pl          67
 14  snil     pl          58
 15  shailesh pl          52
 16  sunny    java        75
 18  aunny    java        24
 19  sunny    pl          45
 20  sunny    linux       30
 21  aunny    pl          67
 22  aunny    linux       87
 24  aunny    sql         75
 25  sunny    aql         97
 26  snil     sql         64
 27  ashish   sql         87
 28  sudip    sql         37
 29  shailesh sql         53

23 rows selected



my query is-

sql>select * from(
select e.*, dense_rank()over(partition by subject
order by mark)r from marksheet e)
where r=1;
 rno  name      subject     mark   r
----  -----     --------   ------  --
 25   sunny      aql        97     1
 18   aunny      java       24     1
 20   sunny      linux      30     1
 19   sunny      pl         45     1
 28   sudip      sql        37     1

5 rows selected

same result i got by the query-

sql>select * from marksheet
where mark in(
select min(mark) from marksheet
group by sujects);

please advice me what will i do next to get the result.

regards




[Updated on: Tue, 23 January 2007 21:29]

Report message to a moderator

Re: min marks from subjects [message #215826 is a reply to message #215777] Wed, 24 January 2007 00:50 Go to previous message
CoolBuddy
Messages: 10
Registered: December 2006
Location: India
Junior Member
this may help:
------------------------------------------
SELECT NAME, SUM(cnt)
FROM (SELECT t1.NAME, t1.subject, t1.mark, COUNT(*) AS cnt
FROM marksheet t1
WHERE mark IN
(SELECT MIN(t.mark) FROM marksheet t GROUP BY t.subject)
GROUP BY t1.NAME, t1.subject, t1.mark)
GROUP BY NAME
HAVING SUM(cnt) >= 2
ORDER BY NAME
------------------------------------------

Previous Topic: prob in selecting from type(collection)
Next Topic: update diff datatype
Goto Forum:
  


Current Time: Thu Dec 08 04:03:30 CST 2016

Total time taken to generate the page: 0.08677 seconds