Return TOP 1 for each name - 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

SQL Server Development (2000)

Return TOP 1 for each name

Author

Topic

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-18 : 00:19:15

Hi,

I have the 2 following table stated below.

table name : tblregfund

FUND_CODE..........FUND_NAME
TY..................Toyota
HO..................Honda
BM...................BMW
MB..................MBenz
NI..................Nissan

=========================================

table name : tblregfundprice

FUND_CODE........FUND_PRICE..........DATE
TY..................2.35...................11/02/2007
HO..................1.24...................22/03/2007
BM..................2.53...................21/04/2007
MB..................1.52...................03/08/2007
NI..................4.21...................11/07/2007
TY..................5.21...................25/06/2007
HO..................4.22...................22/02/2007
BM..................5.2...................24/01/2007
MB..................1.9...................29/08/2007
NI..................1.82...................27/07/2007
TY..................1.66...................28/12/2007
HO..................5.24...................14/11/2007
BM..................1.38...................19/05/2007
MB..................1.2...................17/02/2007
NI..................1.11...................16/04/2007

I want to get TOP 1 latest FUND_PRICE for each FUND_CODE so that it appear as below result.

FUND_NAME........FUND_PRICE
Toyota..................5.21
Honda...................5.24
BMW.....................5.2
MBenz...................1.9
Nissan..................4.21

Just TOP 1 for each FUND_PRICE. Please help.

Thx.

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 00:37:38

This perhaps?


SELECT FUND_NAME, MAX(FUND_PRICE)
FROM tblregfundprice AS P
    JOIN tblregfund AS F
        ON F.FUND_CODE = P.FUND_CODE
GROUP BY FUND_NAME

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 01:45:01

Same professor as this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85268

E 12°55'05.25"
N 56°04'39.16"

vinothrao84
Starting Member

21 Posts

Posted - 2007-10-18 : 03:14:45

quote:

Originally posted by Kristen

This perhaps?


SELECT FUND_NAME, MAX(FUND_PRICE)
FROM tblregfundprice AS P
    JOIN tblregfund AS F
        ON F.FUND_CODE = P.FUND_CODE
GROUP BY FUND_NAME

Kristen

Thx Kristen it's working perfectly....

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 03:21:01

Is it homework?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources