# Multiple table rows in one row

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:

``````select
a.class
,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

where
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
@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:

DDL/DMLS:

``````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);

CREATE FUNCTION SPLIT_STR(  x VARCHAR(255),  delim VARCHAR(12),  pos INT)RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),    delim, '');
``````

SQL:

``````    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;
``````

# Logic:

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