Thread: merge every few rows to one

hello,

I have data that lookes like this:

user grade
------ ------
Adam 20
Adam 11
Adam 99
Roy 10
Roy 55
Adam 100



what I need to show is only the top grade of each user (and it has to be sorted)

like that:

user grade
------ ------
Adam 100
Roy 55


I had so much troubles trying to figure it out.

what do i add to the sql:

SELECT tblGrades.user, tblGrades.Grade
FROM tblGrades
ORDER BY tblGrades.Grade desc;

(if I add: GROUP BY tblGrades.user, tblGrades.Grade it does not help me)

anyone knows how to perform that?
thank you :-)

SELECT tblGrades.user, max(tblGrades.Grade) FROM tblGrades
group by tblgrades.user
ORDER BY tblGrades.user;

hi
thank you!

why can't I sort it by the field of the grade?
(you gave me a solution to sort it by the name)

I added the Blue code but it wont work.

SELECT tblGrades.user, max(tblGrades.Grade) AS grad
FROM tblGrades
group by tblgrades.user
ORDER BY grad;

thanx again!

There is no error in ur code.Try this also
SELECT tblGrades.user, max(tblGrades.Grade) grad
FROM tblGrades
group by tblgrades.user
ORDER BY max(tblGrades.Grade);

hi

from this u can get it.
eg:
select usr,max(grade) from grd group by usr

Originally Posted by rumbleEye

hello,

I have data that lookes like this:

user grade
------ ------
Adam 20
Adam 11
Adam 99
Roy 10
Roy 55
Adam 100



what I need to show is only the top grade of each user (and it has to be sorted)

like that:

user grade
------ ------
Adam 100
Roy 55


I had so much troubles trying to figure it out.

what do i add to the sql:

SELECT tblGrades.user, tblGrades.Grade
FROM tblGrades
ORDER BY tblGrades.Grade desc;

(if I add: GROUP BY tblGrades.user, tblGrades.Grade it does not help me)

anyone knows how to perform that?
thank you :-)

select uname, max(grade) from aa group by uname

hi
thank you all.


thanx susarlasireesha, I didn't try the order by max(grade)
didn't think it is possible.
this is what made it work. :-)


my problem now is that I have added one more field to tblGrades:
subject




if I only add to the SELECT the tblGrades.subject, and to the GROUP BY i add tblGrades.subject,
all the data that is arriving is messed up. I simply get more than one user.
(for example: Adam appears more than once in my results).

this is my code

Code:

SELECT tblGrades.user, tblGrades.subject, max(tblGrades.Grade) grad
FROM tblGrades
group by tblgrades.user, tblGrades.subject
ORDER BY max(tblGrades.Grade);

why can't it simply still group the user to appear unique (only one user name, his grade and subject)?

What I get is that bad result:

Code:

user     Grade  Subject
-----      --------  ----------
Adam 100 math
Adam 99 history
Adam 20 geology
Adam 11 lidriture
Roy 55 sports
Roy 10 science

and what I want to get is this:

Code:

user     Grade  Subject
-----      --------  ----------
Adam 100 math
Roy 55 sports

why cant i make it work?

Originally Posted by rumbleEye

hello,

I have data that lookes like this:

user grade
------ ------
Adam 20
Adam 11
Adam 99
Roy 10
Roy 55
Adam 100



what I need to show is only the top grade of each user (and it has to be sorted)

like that:

user grade
------ ------
Adam 100
Roy 55


I had so much troubles trying to figure it out.

what do i add to the sql:

SELECT tblGrades.user, tblGrades.Grade
FROM tblGrades
ORDER BY tblGrades.Grade desc;

(if I add: GROUP BY tblGrades.user, tblGrades.Grade it does not help me)

anyone knows how to perform that?
thank you :-)

Dear rumbleEye,

Try this and reply

select max(grade),name
from table_name
group by name
order by name

I hope this will work for you.

Have a pleasant time....

select distinct(tblGrades.user),tblGrades.subject,max(tblGrades.Grade)grad
from tblGrades
group by tblgrades.user,tblGrades.subject
order by max(tblGrades.Grade)

Hello,

try this ..

Select distinct c.usern, c.grade, b.subject from tblgrades b,
(select usern, max(grade) grade from tblgrades group by usern) c
where b.usern= c.usern
and b.grade= c.grade

sample data i have used
usern grade subject
adam 20 geology
adam 11 litriture
adam 99 history
adam 100 math
roy 10 science
roy 55 sports
james 10 oracle

result
usern grade subject
adam 100 math
roy 55 sports
james 10 oracle

thanks,

Originally Posted by rumbleeye

hello, i have data that lookes like this: user grade
------ ------
adam 20
adam 11
adam 99
roy 10
roy 55
adam 100 what i need to show is only the top grade of each user (and it has to be sorted) like that: user grade
------ ------
adam 100
roy 55 i had so much troubles trying to figure it out. What do i add to the sql: select tblgrades.user, tblgrades.grade
from tblgrades
order by tblgrades.grade desc; (if i add: group by tblgrades.user, tblgrades.grade it does not help me) anyone knows how to perform that?
thank you :-)

Dear Friend,

Try this and check

select NAME,SUBJECT,MARKS
from TABLE_NAME where (NAME,MARKS) in (
select NAME nm ,max(MARKS) mrk
from TABLE_NAME
group by NAME)
order by NAME

The above query first finds the maximum marks out of all the subjects, of a particular student. Then after based on the Name and Marks, it will select the
subject name.

Try this i hope it will work

Reply soon

Originally Posted by rumbleEye

hello,

I have data that lookes like this:

user grade
------ ------
Adam 20
Adam 11
Adam 99
Roy 10
Roy 55
Adam 100



what I need to show is only the top grade of each user (and it has to be sorted)

like that:

user grade
------ ------
Adam 100
Roy 55


I had so much troubles trying to figure it out.

what do i add to the sql:

SELECT tblGrades.user, tblGrades.Grade
FROM tblGrades
ORDER BY tblGrades.Grade desc;

(if I add: GROUP BY tblGrades.user, tblGrades.Grade it does not help me)

anyone knows how to perform that?
thank you :-)

Dear Friend,

Try this and check

select NAME,SUBJECT,MARKS
from TABLE_NAME where (NAME,MARKS) in (
select NAME nm ,max(MARKS) mrk
from TABLE_NAME
group by NAME)
order by NAME

The above query first finds the maximum marks out of all the subjects, of a particular student. Then after based on the Name and Marks, it will select the
subject name.

Try this i hope it will work

Reply soon

Thank you all !

* I gave a try to the distinct command, But it didnt work.

* about the other solution, I beleive that adding another Select within the existing one will slow the resalts (later when the data will grow bigger) am I right?

aventually, it only worked with what ADMIN suggested.
(a combination of distinct command and the select inside a select)

still, if Adam got twice 100 (wich is possible- I mean in two different subjects) it shows Adam twice.

user grade subject
----- ------ --------
Adam 100 math
Adam 100 sports
Roy 50 history



I have tryed sreekumar_nair_it's solution.
I started testing it but didnt quite manage yet.
Im going to try it some more.

Originally Posted by rumbleEye

Thank you all !

* I gave a try to the distinct command, But it didnt work.

* about the other solution, I beleive that adding another Select within the existing one will slow the resalts (later when the data will grow bigger) am I right?

aventually, it only worked with what ADMIN suggested.
(a combination of distinct command and the select inside a select)

still, if Adam got twice 100 (wich is possible- I mean in two different subjects) it shows Adam twice.

user grade subject
----- ------ --------
Adam 100 math
Adam 100 sports
Roy 50 history



I have tryed sreekumar_nair_it's solution.
I started testing it but didnt quite manage yet.
Im going to try it some more.

Dear rumbleEye,

Thanks for your kind reply.

Yes you are right. Writing a select inside another will slow down the performance, but only if you are using in-built functions with column
names (for. e.g. count(column_name),distinct(column_name),max(column_name) etc) or making multiple join conditions or subqueries.

I tried the same on one of the transaction table of my organization consisting of 76000 rows
and the query execution time is 138 msec.

Dont worry,It wont be that much slow.

Even if you are not satified with the solution, we will find other solution together dont worry.

Now your question is, what if ADAM scores 100 in both MATHS and SPORTS.

Kindly specify your requirement in such conditions, i mean what is the Output needed in such conditions.

Have a pleasant time

select a.name,max(a.marks),a.subject from tn a
where (a.name,a.marks ) in (select name,max(marks) from tn group by name)
group by a.name,a.subject

this one works... as admin said..

Select distinct c.usern, c.grade, b.subject from tblgrades b,
(select usern, max(grade) grade from tblgrades group by usern) c
where b.usern= c.usern
and b.grade= c.grade

just tired myself with the same sample table