Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Get MAXIMUM value in WHERE

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Thu Nov 20, 2008 10:22 am    Post subject: Get MAXIMUM value in WHERE
Reply with quote

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
Back to top
View user's profile Send private message

Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Nov 20, 2008 10:46 am    Post subject:
Reply with quote

How about

Code:

Select Name,Max(Marks) from tablename
group by Name
Back to top
View user's profile Send private message
darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Thu Nov 20, 2008 10:54 am    Post subject:
Reply with quote

Hi Harijax,

Using your table entries,
Code:
 SELECT STUDENT     
     , MAX ( MARKS )
  FROM TABLE_NAME   
 GROUP BY STUDENT ;


Should give the results you gave.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Nov 20, 2008 10:55 am    Post subject:
Reply with quote

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);
Back to top
View user's profile Send private message
darkstar13

New User


Joined: 06 Nov 2008
Posts: 46
Location: Manila, Philippines

PostPosted: Thu Nov 20, 2008 10:58 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Thu Nov 20, 2008 11:12 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Thu Nov 20, 2008 12:23 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the maximum number of sort wo... Pravina M SYNCSORT 2 Mon Mar 14, 2016 4:51 pm
No new posts How to find the maximum value of a pa... Susheel singh IMS DB/DC 8 Mon Mar 24, 2014 11:39 am
No new posts Select maximum value of Sequence Num... Vijayshyale DB2 1 Fri Jan 31, 2014 8:11 am
No new posts Get maximum value of 2 different fields aditya_gorti DFSORT/ICETOOL 10 Wed Nov 27, 2013 7:36 pm
No new posts Maximum number of I/O PCBs in a COBOL... guruji Mainframe Interview Questions 7 Mon Nov 18, 2013 11:25 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us