Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a écrit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID = (SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:
>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID => (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
> >My task table contains the id of the task, the jobid that the task is
> >associated with, and a numeric value representing the priority of the
> >task. There can be multiple tasks for a job. I need a view that
> >returns the task id, and job id of the task with the higest priority
> >for each job. I tried grouping but this does not appear to work.
> >
> >Thanks for the help.
> >
> >Sean M. Severson