Help needed concerning "Group by " statement

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 2000 Forums

Transact-SQL (2000)

Help needed concerning "Group by " statement

Author

Topic

sand-lakes1
Starting Member

29 Posts

Posted - 2002-11-21 : 11:48:20

Hi everybody,

I am working with a group by function, working on the attached table.

Org Part_nbr Start_date pporg amount

0021 000000001 12/6/2001 0021 46
0021 000000001 9/23/2001 0021 47
0021 000000001 10/10/2000 0021 47
0021 000000001 9/24/2000 0021 57
0463 000003110 11/12/2001 0463 3709
0463 000003110 1/18/2002 0463 4006

What is the command to get only these two records (Only the records which contain unique part_nbr, where org=pporg and which have the most recent start_date if there is more then one record ?

Org Part_nbr Start_date pporg amount

0021 000000001 12/6/2001 0021 46
0463 000003110 1/18/2002 0463 4006

I would appreciate if someone can help, because I have to do this select command on a total number of 18000000 records....

Nice evening and thanks in advance

JOhn

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-21 : 12:18:12

something like

select *
from tbl t1
where Start_date =
(select max(StartDate) from tbl t2
where t1.Org = t2.Org and t1.pporg = t2.pporg and t1.Part_nbr = t2.Part_nbr)
and t1.Org = t1.pporg

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sand-lakes1
Starting Member

29 Posts

Posted - 2002-11-22 : 07:06:30

This worked fine. Thanks Nigel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources