Get MAXIMUM value in WHERE

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

Hi,

I got a requirement as below.

I have a table like
Student Marks
AAAA 90
AAAA 86
AAAA 82
BBBB 92
BBBB 40
CCCC 80
DDDD 47
DDDD 89

My actual table is slightly more complex. I want to create a report of maximum marks of every student.

I wanted to fetch through a single SELECT.
AAAA 90
BBBB 92
CCCC 80
DDDD 89

Regards
harijax

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

How about

darkstar13 · Posted: Thu Nov 20, 2008 10:54 am

Hi Harijax,

Using your table entries,

Srihari Gonugunta · Posted: Thu Nov 20, 2008 10:55 am

Hi Hari,
You can use the following query

Select student,marks from std_table A
where marks = (select max(marks) from std_table B where B.Student=A.student);

darkstar13 · Posted: Thu Nov 20, 2008 10:58 am

Hi again Hari,
You should give more specific details as to which tables are the columns STUDENT & MARKS from. I assumed it was from the same table.

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

Hi All, Slightly modified the requirement.
AAAA 90 Bangalore
AAAA 86 Chennai
AAAA 82 Bombay
BBBB 92 Kochi
BBBB 40 Delhi
CCCC 80 US
DDDD 47 UK
DDDD 89 Australia

I want to create a report as below again max(marks) is the main criteria.

AAAA Bangalore
BBBB Kochi
CCCC US
DDDD Australia

Srihari, can I have some other way to do it. using simple select.

mallik4u · New User Joined: 17 Sep 2008 Posts: 75 Location: bangalore

Hi Hari,

I don't think you can get your results with out using sub query or joins. If you are looking for a join query then please find the below one. It worked for me.

SELECT A.NAME, A.CITY
FROM STUDENT A,
(SELECT NAME,MAX(MARKS) AS MARKS
FROM STUDENT GROUP BY NAME) B
WHERE A.NAME = B.NAME AND
A.MARKS = B.MARKS