Mistake - Select Query with MAX ()-SQLServer2005

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

General SQL Server Forums

New to SQL Server Programming

Mistake - Select Query with MAX ()-SQLServer2005

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2009-04-15 : 07:01:43

Hi,
I cannot identify the mistake....Any better ideas?

select sec_erb_id.emp_id,emp_master.fir_name,sec_erb_id.sec_id_no,sec_erb_id.sec_id_expiry,emp_master.blood_grp,sec_erb_id.sec_project,sec_erb_id.sec_division,sec_erb_id.sec_remarks from(sec_erb_id inner join emp_master on emp_master.emp_id=sec_erb_id.emp_id where sec_erb_id.id_renewal_no=max(sec_erb_id.id_renewal_no)) order by len(sec_erb_id.emp_id),sec_erb_id.emp_id

Paramu @ PARANTHAMAN

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 07:15:50

select sec_erb_id.emp_id,
emp_master.fir_name,
sec_erb_id.sec_id_no,
sec_erb_id.sec_id_expiry,
emp_master.blood_grp,
sec_erb_id.sec_project,
sec_erb_id.sec_division,
sec_erb_id.sec_remarks
from sec_erb_id
inner join emp_master on emp_master.emp_id=sec_erb_id.emp_id
where sec_erb_id.id_renewal_no in ( select max(sec_erb_id.id_renewal_no) from sec_erb_id --- if any group by added that cond)
order by len(sec_erb_id.emp_id),sec_erb_id.emp_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 07:16:38

[code]select emp_id,
fir_name,
sec_id_no,
sec_id_expiry,
blood_grp,
sec_project,
sec_division,
sec_remarks
from (
select sec_erb_id.emp_id,
emp_master.fir_name,
sec_erb_id.sec_id_no,
sec_erb_id.sec_id_expiry,
emp_master.blood_grp,
sec_erb_id.sec_project,
sec_erb_id.sec_division,
sec_erb_id.sec_remarks,
row_number() over (partition by sec_erb_id.emp_id order by sec_erb_id.id_renewal_no desc) as recid
from sec_erb_id
inner join emp_master on emp_master.emp_id = sec_erb_id.emp_id
) as d
where recid = 1
order by len(emp_id),
emp_id[/code]

E 12°55'05.63"
N 56°04'39.26"

paramu
Posting Yak Master

151 Posts

Posted - 2009-04-15 : 07:37:32

Thanks For Both Replies & Simply I go thru our BKLR advice, it worked fine.

BKLR advice is for single record.But PESO's advice is whole table.

Thanks Again To Both Replies

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources