I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd.

class  position  name  total_marks
10     1         a     100
10     2         b     200
10     3         c     150
11     1         d     300 
11     2         e     400
11     3         f     200

I want to write a query to find similar situation in all classes.


Try this query with 2 self joins:

,a.total_marks as 1st_Position_Marks
,b.total_marks as 2nd_Position_Marks
,c.total_marks as 3rd_Position_Marks
,abs (a.total_marks - b.total_marks) as Difference

from MyTable a
join MyTable b on a.class = b.class and a.position + 1 = b.position
join MyTable c on a.class = c.class and a.position + 2 = c.position

a.position= 1 and b.position=2 and c.position =3 and
c.total_marks > abs(a.total_marks - b.total_marks)

UPDATE. Some explaination.

Basically you are doing calculations on columns, so you need to put data in two columns alongside each other. To do than you need a join, not union. You need to join two rows, that would contain data for your calcultaion. As we need difference between position 1 and 2 we need to put this data on the same row. A.position +1 = B.position does exactly that. as you do not need other permutations of position, I added the filter.

  • You don't really need a.position + 1 = b.position and a.position + 2 = c.position, do you? – ypercubeᵀᴹ Jun 11 '13 at 15:55
  • @Stoleg - Can you get the expected result output for the User's question? I see it returns the value in two states for a single CLASS, not sure where they arrive. sqlfiddle.com/#!2/5a702/5 - reference. – Mannoj Jun 12 '13 at 7:05
  • @ypercube You either need a filter a.position= 1 and b.position=2 and c.position =3 or a.position + 1 = b.position. As latter is more generic, I included them both. If you need to see all the differencies between total_marks then you remove the filter. – Stoleg Jun 12 '13 at 7:50
  • 1
    @Mannoj: Corrected, there was a typo in the ON clause. – ypercubeᵀᴹ Jun 12 '13 at 8:12
  • Now looks perfect. I started on a different thought which dragged me numerous options to meet the expectations. I agree join is a decent fix here. +1 – Mannoj Jun 12 '13 at 8:25

Below is the code snippet and logic I have followed for your question (Looks like a simple question, but not for me).

Code Snippet:


create table stud (class int, position int, name varchar(20), total_marks int); 

insert into stud values (10,1,'a',100);
insert into stud values (10,2,'b',200);
insert into stud values (10,3,'c',150);
insert into stud values (11,1,'d',300);
insert into stud values (11,3,'f',200);
insert into stud values (11,2,'e',400);
insert into stud values (9,2,'d',300);
insert into stud values (9,3,'e',100);
insert into stud values (9,1,'f',100);

 LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),    delim, '');


    select a.class as ACLASS, group_concat(a.position) as GP, FIND_IN_SET(3,group_concat(a.position)) as THIRD_INDEX_MARK,  SPLIT_STR(group_concat(a.total_marks),',',FIND_IN_SET(3,group_concat(a.position))) as THIRD_POSITION_MARK,case 
when FIND_IN_SET(3,group_concat(a.position))=1 then if(ABS(SPLIT_STR(group_concat(a.total_marks),',',2)-(SPLIT_STR(group_concat(a.total_marks),',',3))) < SPLIT_STR(group_concat(a.total_marks),',',1) , SPLIT_STR(group_concat(a.total_marks),',',1),'Third Position Is Lesser')
when FIND_IN_SET(3,group_concat(a.position))=2 then if(ABS(SPLIT_STR(group_concat(a.total_marks),',',1)-(SPLIT_STR(group_concat(a.total_marks),',',3))) < SPLIT_STR(group_concat(a.total_marks),',',2) , SPLIT_STR(group_concat(a.total_marks),',',2),'Third Position Is Lesser') 
when FIND_IN_SET(3,group_concat(a.position))=3 then if(ABS(SPLIT_STR(group_concat(a.total_marks),',',1)-(SPLIT_STR(group_concat(a.total_marks),',',2))) < SPLIT_STR(group_concat(a.total_marks),',',3) , SPLIT_STR(group_concat(a.total_marks),',',3),'Third Position Is Lesser')
else 'SOMEWHERE_SCREWED' END as CASE_RESULT, SPLIT_STR(group_concat(a.name),',',FIND_IN_SET(3,group_concat(a.position))) as THIRD_POSITION_NAME, group_concat(a.total_marks) as TM  from (select name, class,total_marks,position from stud group by position,class) a group by ACLASS;


Inner Query:

  • Concatenate all the rows column for POSITION, NAME, TOTAL_MARK group by CLASS,POSITON. In the sql the inner query is represented with an alias a.

        (select name, class,total_marks,position from stud group by position,class) a 

Outer Query: -

Here is where your actual manipulation happens.

  • From the resulted inner query rows

    • Get the Index position of 3rd occurence from POSITION, with the help of Index position use it to get 3rd Position "NAME, TOTAL_MARK".
    • Now the left out Index positions are going to be other two guys preferebly called as 1 and 2.
    • Get 1 and 2 Total_Mark and find the difference between them. Now compare it with 3rd Postion Total_Mark, if its greater mention the Total_Mark of 3rd Postion else say its Lesser.
  • Now arrange them using returned CLASS in group by at last.

Note: I have used SPLIT_STR function to exactly cut for the said occurence position by said delimiter value. In unix it is like echo "Tom,Dick,Harry" |cut -d',' -f2 -> Returns "Dick".

Try it on your application requirement and see if this suits, I guess I dragged it long. I wish you to get someone with a decent fix.

Use my fiddle -> http://sqlfiddle.com/#!2/750ba/4 for quick testing purposes.

All the best!!

  • Why string functions when it can be done with joins? – ypercubeᵀᴹ Jun 11 '13 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy