Qry with non aggregate cols and not in group by.

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

Qry with non aggregate cols and not in group by.

Author

Topic

jayajaya_7
Starting Member

1 Post

Posted - 2011-01-14 : 20:31:11

Hi all,
This is the table.
PROFILE_ID_NO WORK_OK_CD CREATE_DT
7041086 Y 2003-10-07-10.56.42
7041086 Y 2003-10-05-10.56.42
7041086 null 2003-10-06-06.58.08
7041086 N 2004-09-30-05.08.22

I want a qry to pick a profile_id_no and work_ok_cd where create_dt is maximum. I tried

select profile_id_no, work_ok_cd , max(create_dt) from profile group by profile_id_no, work_ok_cd
It gave 3 rows . I dont want that.

I want only the last row to be displayed. How to do that?
Pls help me write this qry .
Thanks
Jaya

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 20:49:51

Select b.*
FROM profile b inner join (
select profile_id_no, max(create_dt) as maxdate
from profile
group by profile_id_no) a
on b.profile_id_no = a.Profile_id_no and create_dt = maxdate

That's one way perhaps. The problem is the NO_WORK_OK field you are grouping by..it forces a return of the 3 rows that have Y, N and Null for that field.
You can also use some more advanced query techniques..but let's keep it simple for now.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:31:00

[code]
select top 1 with ties PROFILE_ID_NO, WORK_OK_CD FROM table ORDER BY CREATE_DT DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources