Please suggest efficient SQL query for given requirement

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

Hi,

Please advise SQL cost efficient SQL query for the below requirement:

Table structure :

Employee Table
CLASS CHAR(2) NOT NULL,
MARKS DECIMAL(3,0) NOT NULL,
STUDENT_NAME CHAR (30) NOT NULL

Requirement:
To get student details with highest marks from each class.

Please suggest.[/u]

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

Any reply please

Nic Clouston · Posted: Wed Jul 31, 2013 6:33 pm

Less than 1 hour after posting you are asking for replies when response are made by people IF they feel like it. If you want a quick response then go and pay someone for it.

I suggest no-one replies to this until Friday.

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

Apologies

for my last reply.

I should have refrain from posting quick reply, i will ensure to be more patient going forward.

Awaiting all valuable response to this.

daveporcelan · Posted: Wed Jul 31, 2013 7:02 pm

This requirement feels like a homework assignment to me.

The purpose of the such an assignment is for the Student to use their brain and figure it out for THEMSELVES.

Soliciting an answer from the internet is not the intent of such assignment.

If this is actually work related, then the employee is overpaid.

chandan.inst · Posted: Wed Jul 31, 2013 8:17 pm

Hi Kunal,

What you have tried so far with this query?

What are the difficulties you are facing?

Regards,
Chandan

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

Tried below query & got desired output, however performance of this query is very bad(taking 25-30 seconds approx) probably due to high volume of table(about 600,000).
Thus please assist me in optimizing below Db2 SQL query.

Note: Table & column names have changes to maintain data integrity.

SELECT CLASS, MARKS, STUDENT_NAME, STUDENT_SEX
FROM STUDENT
WHERE (CLASS, MARKS) IN
( SELECT CLASS, MAX(MARKS)
FROm STUDENT
GROUP BY CLASS
)
AND
CLASS > ' ' condition needed to eliminate blank values of CLASS
ORDER BY CLASS ASC
WITH UR

Thank you in advance for help !

chandan.inst · Posted: Thu Aug 01, 2013 1:58 pm

Just try to EXPLAIN the query and see which table causing the issue

Also can't you club CLASS the condition in Subquery itself?

Reagards,
Chandan

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

There is only one table which is STUDENT

I also tried to put condition WHERE CLASS> ' ' into Sus-query but same result.

Please advise

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

Does anyone has improvement suggestion on above query then please let me know

dbzTHEdinosauer · Posted: Fri Aug 02, 2013 3:26 pm

is there an index? more than one?
what was the result of the explain?

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

yes there are 5 indexes defines on the table.
Explain recommends to create index for column on which MAX function is applied.

On the query perspective, I would like to know can there be any better query of achieving above result ?

dick scherrer · Posted: Fri Aug 02, 2013 8:42 pm

Hello,

If the EXPLAIN says the query would benefit from the addition of another index, you should create the new index and test . . .

If that does Not help, then look at other alternatives.

Eshwar CICS · New User Joined: 18 May 2011 Posts: 47 Location: India

As an alternative, try using OLAP functions (DENSE_RANK and PARTITION BY).

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India

chandan.inst · Posted: Thu Aug 08, 2013 12:15 pm

Hi Kunal,

I am not sure which DB2 version you are using, but we do have these fuctions available in DB2.

Please check with shop if these fuctions are present or not

Regards,
Chandan

dick scherrer · Posted: Thu Aug 08, 2013 8:23 pm

Hello,

kunal jain · New User Joined: 19 May 2011 Posts: 59 Location: India