Help to get the following results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help to get the following results

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-12-10 : 09:21:02

I'm currently showing the following
Job Job_ID Part_ID
1000 47912 7543
1000 47913 0
1000 47913 7546
1000 47914 7549
1000 47915 7552
1000 47916 7555
1000 47916 7558
1000 47916 0
1000 47923 7576

What I like is to add a new field and show the Max Part ID where JOB_ID are the same.

Job Job_ID Part_ID New_ID
1000 47912 7543 7543
1000 47913 0 7546
1000 47913 7546 7546
1000 47914 7549 7549
1000 47915 7552 7552
1000 47916 7558 7558
1000 47916 7558 7558
1000 47916 0 7558
1000 47923 7576 7576

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 09:25:02

[code]SELECT Job, Job_ID, Part_ID,MAX(Part_ID) OVER (PARTITION BY Job_ID) AS New_ID
FROM YourTable
[/code]

chapo
Starting Member

39 Posts

Posted - 2008-12-10 : 11:36:28

quote:

Originally posted by visakh16

SELECT Job, Job_ID, Part_ID,MAX(Part_ID) OVER (PARTITION BY Job_ID) AS New_ID
FROM YourTable

Thank for your help but I get the following message.
The OVER SQL construct or statement is not supported.

svicky9
Posting Yak Master

232 Posts

Posted - 2008-12-10 : 12:53:43

Are you using SQL 2000.

select a.Job,a.Job_id,a.Part_id,b.MaxPartid
(
SELECT Job, Job_ID, Part_ID AS New_ID
FROM YourTable
) a
join
(
select jobid, max(part_id) as MaxPartid from yourtable
group by jobid
)b
on
a. jobid = b.jobid

Vic

http://vicdba.blogspot.com

chapo
Starting Member

39 Posts

Posted - 2008-12-10 : 13:36:12

quote:
Originally posted by svicky9

Are you using SQL 2000.

select a.Job,a.Job_id,a.Part_id,b.MaxPartid
(
SELECT Job, Job_ID, Part_ID AS New_ID
FROM YourTable
) a
join
(
select jobid, max(part_id) as MaxPartid from yourtable
group by jobid
)b
on
a. jobid = b.jobid

Vic

http://vicdba.blogspot.com

I'm using SQL 2005 but i'm trying to get the results on a view and from what I read on the net you can only use the OVER command on NEW Query window.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:14:31

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

SELECT Job, Job_ID, Part_ID,MAX(Part_ID) OVER (PARTITION BY Job_ID) AS New_ID
FROM YourTable

Thank for your help but I get the following message.
The OVER SQL construct or statement is not supported.

seems like your compatibility level is below 90. run this and post back results

EXEC sp_dbcmptlevel yourdatabasename

chapo
Starting Member

39 Posts

Posted - 2008-12-16 : 09:16:20

quote:
Originally posted by visakh16

quote:
Originally posted by chapo

quote:
Originally posted by visakh16

SELECT Job, Job_ID, Part_ID,MAX(Part_ID) OVER (PARTITION BY Job_ID) AS New_ID
FROM YourTable

Thank for your help but I get the following message.
The OVER SQL construct or statement is not supported.

seems like your compatibility level is below 90. run this and post back results

EXEC sp_dbcmptlevel yourdatabasename

My compatibility level is set to 70. If I where to change it to 90 would it affect any of my applications currently using this database

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-16 : 09:17:57

Yes if it is using features supported only by SQL 7.0 not 2000 and 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources