First 5 Related Records?

I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do this
?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926Try:
select
o1.F_relNum
from
Orders o1
where
o1.RE_rel_num in
(
select top 5
o2.RE_rel_num
from
Orders o2
where
o2.F_relNum = o1.F_relNum
order by
o2.RE_rel_num desc
)
order by
o1.F_relNum
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Wendy" <Wendy@.discussions.microsoft.com> wrote in message
news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
I am trying to determine what the first 5 related records to another record
are. I have created a table of matches where I have returned the unique key
value for each record in a one to many relationship. Preferrably, I would
like to update the table that contains the F_rel_num with the 5 (or less)
values for RE_rel_num in fields such as RE_rel_num1,
RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would not
work as I need the top 5 for each F_rel_num. Does anyone know how to do
this?
I could easily write this in ASP or VB, but I need to be able to run this as
a regular SQL job, so I imagine I need to do it completely with T-SQL.
F_relNum RE_rel_num
3 1633955
3 1353526
3 1137500
3 905264
3 732204
3 639101
3 488182
3 377705
3 365446
3 365445
3 313125
3 256899
3 254183
3 133409
6 214174
6 139273
6 117524
6 117520
7 1053160
11 1126433
11 857312
11 464240
13 555629
13 316781
13 302905
13 231447
14 644116
14 164926|||That worked great! Thank You
"Tom Moreau" wrote:

> Try:
> select
> o1.F_relNum
> from
> Orders o1
> where
> o1.RE_rel_num in
> (
> select top 5
> o2.RE_rel_num
> from
> Orders o2
> where
> o2.F_relNum = o1.F_relNum
> order by
> o2.RE_rel_num desc
> )
> order by
> o1.F_relNum
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Wendy" <Wendy@.discussions.microsoft.com> wrote in message
> news:D93DB121-1CDD-4B4E-82AB-37C7207A9B09@.microsoft.com...
> I am trying to determine what the first 5 related records to another recor
d
> are. I have created a table of matches where I have returned the unique ke
y
> value for each record in a one to many relationship. Preferrably, I would
> like to update the table that contains the F_rel_num with the 5 (or less)
> values for RE_rel_num in fields such as RE_rel_num1,
> RE_rel_num2...RE_rel_num5. The top 5 is based on decending values for
> RE_rel_num. I have included sample data below. TOP 5 in hte SELECT would n
ot
> work as I need the top 5 for each F_rel_num. Does anyone know how to do
> this?
> I could easily write this in ASP or VB, but I need to be able to run this
as
> a regular SQL job, so I imagine I need to do it completely with T-SQL.
> F_relNum RE_rel_num
> 3 1633955
> 3 1353526
> 3 1137500
> 3 905264
> 3 732204
> 3 639101
> 3 488182
> 3 377705
> 3 365446
> 3 365445
> 3 313125
> 3 256899
> 3 254183
> 3 133409
> 6 214174
> 6 139273
> 6 117524
> 6 117520
> 7 1053160
> 11 1126433
> 11 857312
> 11 464240
> 13 555629
> 13 316781
> 13 302905
> 13 231447
> 14 644116
> 14 164926
>