Last (Aggregate) - - SQL Server Forums

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)

Last (Aggregate) -

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-11 : 07:38:11

Denny writes "Greetings; I am converting some MSAccess queries to TSql and I've run across a query that selects the last inserted occurance of an aggregate as such -

SELECT DOC_CONTRACT AS Contract,
Last(SALES_REP_NUM) AS Sales_Badge,
Last(CONTROL_STATUS) AS Renewal_Status,
Last(DATE_EXPIRE) AS DtExp
FROM AM_SI_ART_RENEWAL_HISTORY
GROUP BY DOC_CONTRACT
HAVING (((Left(DOC_CONTRACT,3)='DCO') AND
((Last(CONTROL_STATUS))<>'V') AND
((Last(SOURCE_ID))<>'CPQCDN' And
(Last(SOURCE_ID))<>'SAPCA00') AND
(((Last(Left([SOURCE_ID],3)))='CPQ' Or
(Last(Left([SOURCE_ID],3)))='SAP') And
(Last(Left([SOURCE_ID],3)))<>'SPT'));

I cannot find anything in TSql that can do this - (perhaps a cursor ?!?) - Any help that you can provide is greatly appreciated !!"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 07:41:54

Read through thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27766

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-09-13 : 17:13:39

Use this as sample for your case.


---------------------
n    x    y     z
---------------------
1    4    55    abc
1    7    33    rhu
1    2    44    qwe
2    1    22    sdf
2    3    11    hgf
---------------------

select n,
(select top 1 x from t tt where tt.n=t.n order by x desc, y desc, z desc),
(select top 1 y from t tt where tt.n=t.n order by x desc, y desc, z desc)
from t
where
n<>2 and
left(
(select top 1 z from t tt where tt.n=t.n order by x desc, y desc, z desc),1
)<>'r'

group by n

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources