Top n در Group By

[dkhatibi]

روی یکی از فیلدها Group By کرده ایم که هر مجموعه حاوی چند رکورد خواهد بود. می خواهیم چند تا از بیشترین و چند تا از کمترین را در هر گروه انتخاب کنیم !
چگونه؟

[reza_rad]

کوئری تون رو اینجا بنویسید.

[dkhatibi]

select SQ.Class_name,max(moadel)as maxmoadel,min(moadel)as minmoadel from
(select AVG(nomreh) as moadel,Nc.St_no,Cst.class_code,cn.Class_name from
Nomreh_class nc,Class_st Cst, Class_name cn,dars_name dn where
nc.st_no=Cst.st_no and cn.class_code=Cst.class_code
Group By nc.St_no,cst.Class_code,cn.class_name)SQ group by SQ.class_Name
من به این طریق ماکسیمم معدل و مینیمم را محاسبه می کنم. اما چندتا از بیشترین یا کمترین چطور؟

[reza_rad]

من به این طریق ماکسیمم معدل و مینیمم را محاسبه می کنم. اما چندتا از بیشترین یا کمترین چطور؟
آیا دانشجویانی که این بیشترین یا کمترین نمره ها رو گرفته اند اسمشون هم باید در نتیجه کوئری بیاد؟

[reza_rad]

اینو تست کنید:
این علاوه بر لیست کردن چند نمره،نام دانشجوها رو هم برمیگردونه.

select * from
(
select top 10
SQ.Class_name,
SQ.moadel as maxmoadel,
SQ.St_no
from

(
select
AVG(nomreh) as moadel,
Nc.St_no,
Cst.class_code,
cn.Class_name
from
Nomreh_class nc,
Class_st Cst,
Class_name cn,
dars_name dn

where
nc.st_no=Cst.st_no
and cn.class_code=Cst.class_code

Group By
nc.St_no,
cst.Class_code,
cn.class_name
)SQ

order by SQ.class_Name,SQ.moadel asc
)tbl1
inner join
(
select top 10
SQ.Class_name,
SQ.moadel as minmoadel,
SQ.St_no
from

(
select
AVG(nomreh) as moadel,
Nc.St_no,
Cst.class_code,
cn.Class_name
from
Nomreh_class nc,
Class_st Cst,
Class_name cn,
dars_name dn

where
nc.st_no=Cst.st_no
and cn.class_code=Cst.class_code

Group By
nc.St_no,
cst.Class_code,
cn.class_name
)SQ

order by SQ.class_Name,SQ.moadel desc
)tbl2

on tbl1.Class_name= tbl2.Class_name
and tbl1.St_no=tbl2.St_no
البته چون جدولهای شما رو نداشتم امکان تست وجود نداشت و ممکنه چند ارور کوچک و سینتکسی داشته باشه. ولی فکر کنم جواب میده.

[dkhatibi]

سلام
خیلی ممنون
کد قشتگی گذاشتید. هیچ خطای دستوری مشاهده نشد. اما جدول جواب خالیه!

[dkhatibi]

شاید با یک کوئری سخت باشه ماکس و مین ها را اگه با دو کوئری راحت تر باشه

ولی من نتونستم.

در کد شما خط

and tbl1.St_no=tbl2.St_no
را حذف کردم. جواب 100 رکورد است که فقط نام یکی از کلاسها می اد



101 16.020833333333332 386071 101 19.866666666666667 386075
101 14.955 386089 101 19.866666666666667 386075
101 15.726851851851851 386002 101 19.866666666666667 386075
101 15.931372549019608 386079 101 19.866666666666667 386075
101 13.676470588235293 386082 101 19.866666666666667 386075
101 14.5 386076 101 19.866666666666667 386075
101 14.890000000000001 386003 101 19.866666666666667 386075
101 11.634615384615385 386006 101 19.866666666666667 386075
101 13.191176470588236 386080 101 19.866666666666667 386075
101 13.36046511627907 386084 101 19.866666666666667 386075
101 16.020833333333332 386071 101 19.709183673469386 386069
101 14.955 386089 101 19.709183673469386 386069
101 15.726851851851851 386002 101 19.709183673469386 386069
101 15.931372549019608 386079 101 19.709183673469386 386069
101 13.676470588235293 386082 101 19.709183673469386 386069
101 14.5 386076 101 19.709183673469386 386069
101 14.890000000000001 386003 101 19.709183673469386 386069
101 11.634615384615385 386006 101 19.709183673469386 386069
101 13.191176470588236 386080 101 19.709183673469386 386069
101 13.36046511627907 386084 101 19.709183673469386 386069
101 16.020833333333332 386071 101 19.613207547169811 386088
101 14.955 386089 101 19.613207547169811 386088
101 15.726851851851851 386002 101 19.613207547169811 386088
101 15.931372549019608 386079 101 19.613207547169811 386088
101 13.676470588235293 386082 101 19.613207547169811 386088
101 14.5 386076 101 19.613207547169811 386088
101 14.890000000000001 386003 101 19.613207547169811 386088
101 11.634615384615385 386006 101 19.613207547169811 386088
101 13.191176470588236 386080 101 19.613207547169811 386088
101 13.36046511627907 386084 101 19.613207547169811 386088
101 16.020833333333332 386071 101 19.59 386085
101 14.955 386089 101 19.59 386085
101 15.726851851851851 386002 101 19.59 386085
101 15.931372549019608 386079 101 19.59 386085
101 13.676470588235293 386082 101 19.59 386085
101 14.5 386076 101 19.59 386085
101 14.890000000000001 386003 101 19.59 386085
101 11.634615384615385 386006 101 19.59 386085
101 13.191176470588236 386080 101 19.59 386085
101 13.36046511627907 386084 101 19.59 386085
101 16.020833333333332 386071 101 19.479166666666668 386087
101 14.955 386089 101 19.479166666666668 386087
101 15.726851851851851 386002 101 19.479166666666668 386087
101 15.931372549019608 386079 101 19.479166666666668 386087
101 13.676470588235293 386082 101 19.479166666666668 386087
101 14.5 386076 101 19.479166666666668 386087
101 14.890000000000001 386003 101 19.479166666666668 386087
101 11.634615384615385 386006 101 19.479166666666668 386087
101 13.191176470588236 386080 101 19.479166666666668 386087
101 13.36046511627907 386084 101 19.479166666666668 386087
101 16.020833333333332 386071 101 19.390625 386081
101 14.955 386089 101 19.390625 386081
101 15.726851851851851 386002 101 19.390625 386081
101 15.931372549019608 386079 101 19.390625 386081
101 13.676470588235293 386082 101 19.390625 386081
101 14.5 386076 101 19.390625 386081
101 14.890000000000001 386003 101 19.390625 386081
101 11.634615384615385 386006 101 19.390625 386081
101 13.191176470588236 386080 101 19.390625 386081
101 13.36046511627907 386084 101 19.390625 386081
101 16.020833333333332 386071 101 19.358490566037737 386091
101 14.955 386089 101 19.358490566037737 386091
101 15.726851851851851 386002 101 19.358490566037737 386091
101 15.931372549019608 386079 101 19.358490566037737 386091
101 13.676470588235293 386082 101 19.358490566037737 386091
101 14.5 386076 101 19.358490566037737 386091
101 14.890000000000001 386003 101 19.358490566037737 386091
101 11.634615384615385 386006 101 19.358490566037737 386091
101 13.191176470588236 386080 101 19.358490566037737 386091
101 13.36046511627907 386084 101 19.358490566037737 386091
101 16.020833333333332 386071 101 19.215686274509803 386086
101 14.955 386089 101 19.215686274509803 386086
101 15.726851851851851 386002 101 19.215686274509803 386086
101 15.931372549019608 386079 101 19.215686274509803 386086
101 13.676470588235293 386082 101 19.215686274509803 386086
101 14.5 386076 101 19.215686274509803 386086
101 14.890000000000001 386003 101 19.215686274509803 386086
101 11.634615384615385 386006 101 19.215686274509803 386086
101 13.191176470588236 386080 101 19.215686274509803 386086
101 13.36046511627907 386084 101 19.215686274509803 386086
101 16.020833333333332 386071 101 19.076086956521738 386083
101 14.955 386089 101 19.076086956521738 386083
101 15.726851851851851 386002 101 19.076086956521738 386083
101 15.931372549019608 386079 101 19.076086956521738 386083
101 13.676470588235293 386082 101 19.076086956521738 386083
101 14.5 386076 101 19.076086956521738 386083
101 14.890000000000001 386003 101 19.076086956521738 386083
101 11.634615384615385 386006 101 19.076086956521738 386083
101 13.191176470588236 386080 101 19.076086956521738 386083
101 13.36046511627907 386084 101 19.076086956521738 386083
101 16.020833333333332 386071 101 19.021276595744681 386004
101 14.955 386089 101 19.021276595744681 386004
101 15.726851851851851 386002 101 19.021276595744681 386004
101 15.931372549019608 386079 101 19.021276595744681 386004
101 13.676470588235293 386082 101 19.021276595744681 386004
101 14.5 386076 101 19.021276595744681 386004
101 14.890000000000001 386003 101 19.021276595744681 386004
101 11.634615384615385 386006 101 19.021276595744681 386004
101 13.191176470588236 386080 101 19.021276595744681 386004
101 13.36046511627907 386084 101 19.021276595744681 386004

[reza_rad]

کد قشتگی گذاشتید. هیچ خطای دستوری مشاهده نشد. اما جدول جواب خالیه!
عجب!
آره یه جاش رو اشتباه کردم:

در کد شما خط
کد:

and tbl1.St_no=tbl2.St_no

را حذف کردم. جواب 100 رکورد است که فقط نام یکی از کلاسها می اد
دارم روش کار می کنم که درستش کنم. به محض درست شدن میزارمش اینجا.

[reza_rad]

بالاخره درستش کردم:


select --*
tbl1.rowNumber,
tbl1.class,
tbl1.student,
tbl1.moadel,
tbl2.student,
tbl2.moadel
from
(
select
SQ.Class_name as class,
SQ.St_no as student,
SQ.moadel,
rank() over (partition by SQ.Class_name order by SQ.moadel) as rowNumber
from
(
select
AVG(nomreh) as moadel,
Nc.St_no,
Cst.class_code,
cn.Class_name
from
Nomreh_class nc,
Class_st Cst,
Class_name cn,
dars_name dn

where
nc.st_no=Cst.st_no
and cn.class_code=Cst.class_code

Group By
nc.St_no,
cst.Class_code,
cn.class_name
)SQ

)tbl1
inner join
(
select
SQ.Class_name as class,
SQ.St_no as student,
SQ.moadel,
rank() over (partition by SQ.Class_name order by SQ.moadel desc) as rowNumber
from (
select
AVG(nomreh) as moadel,
Nc.St_no,
Cst.class_code,
cn.Class_name
from
Nomreh_class nc,
Class_st Cst,
Class_name cn,
dars_name dn

where
nc.st_no=Cst.st_no
and cn.class_code=Cst.class_code

Group By
nc.St_no,
cst.Class_code,
cn.class_name
)SQ
)tbl2
on tbl1.rowNumber=tbl2.rowNumber
and tbl1.class=tbl2.class

where tbl1.rowNumber<=10

فقط امیدوارم sql server ات 2005 باشه. چون مجبور شدم از تابع rank استفاده کنم که توی 2000 وجود نداره.
اون رقم 10 آخر هم توی کوئری نشاندهنده اینه که چند تا از نمره های max و min مربوط به هر کلاس رو نشون بده.

[dkhatibi]

با تشکر فراوان
به دلیل عجله مشکل را با ترکیب کدنویسی و کوئری نویسی در زبان میزبان حل شد. سرعت بالایی نداره اما جواب می ده.

فقط امیدوارم sql server ات 2005 باشه.
متاسفانه 2000 ه.
ولی قصد رجوع به 2005 را دارم ...
باز هم ممنون