February 1, 2007 at 8:24 am
Hi,
I have a table with 2 columns sectionName, cont_section_id. The data contained is like this
sectionnamecont_section_id
Ajas 4
Ajas 3
Hadi 2
Hadi 1
So this query
select sectionname, count(sectionname)
from sectionnames
group by sectionname
will give o/p like this
Ajas 2
Hadi 2
Now my question is : I want to have the same result as above but I want to order the results by cont_section_id, so the result should show as
Hadi 2
Ajas 2
since Hadi has cont_section_id 1,2 and Ajas has cont_section_id 3,4.
I tried this but it doesnt work because group is formed with sectionname and then cont_section_id
select sectionname, cont_section_id, count(sectionname)
from sectionnames
group by sectionname,cont_section_id
Anyone got any suggestions?
Thanks in advance.
February 1, 2007 at 9:29 am
Will this work?
select sectionname, count(sectionname)
from sectionnames
group by sectionname
order by max(cont_section_id)
John
February 1, 2007 at 10:08 am
John, Great work. Its working like a charm. Only confusion at this point is that the last statement whether I do
order by max(cont_section_id) or order by min(cont_section_id), I get the same order i.e. same result set and same order of results. I was wondering why and also if I could read more about how to use group funcs in order by clause. Any article or tip on this. I cant believe I didnt know this. Makes me feel stupid...
Example for
Ajas 4
Ajas 3
Hadi 2
Hadi 1
o/p for max should be
Ajas 2
Hadi 2
o/p for min should be
Hadi 2
Ajas 2
Right now I get only min result for both max & min.
Thanks once again.
Edited Post --> Well easy way to get results that way is to use desc after order by... I dont know what I am upto today.. it was so simple really...Thanks again.
February 2, 2007 at 2:02 am
Did you try:
select sectionname, count(sectionname)
, min(cont_section_id), max(cont_section_id)
from sectionnames
group by sectionname
order by max(cont_section_id)
To see why the ORDER BY shows what it does?
Andy
February 2, 2007 at 2:06 am
With your sample data, ordering by max or by min will give the same result. However, if your data looked like this, you would get a different results and then you'd need to think carefully about whether to order by max or min.
Ajas 4
Ajas 1
Hadi 3
Hadi 2
John
February 2, 2007 at 9:31 am
Thanks David and John... it makes lot of sense to me now.... thanks for the help.. things look so much clear now....
David --> your query was good.
John --> your example was good.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply