SQL last(Column) Command - 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)

SQL last(Column) Command

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-11 : 08:03:30

Hi all,
I am converting old MSAccess queries to SQL.
I have a query that picks the latest exchange rate based on the most recent date..Previously in msaccess this was done using the LAST Function but From What I have read the LAST Function is no longer supported in SQL2000.. Is there another way for me to get back the exchange rates...

My current data looks like this.
Country ExChange_rate Exdate
GBP .69 20050125
GBP .66 20060105
IRE .78 20061109
IRE .80 20060101

So I need A Query that will return the most recent ie:

GBP .66 20060105
IRE .78 20061109

Please help

Ray

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 08:08:43

Select * From yourTable t1 where
ExDate = (Select Max(ExDate) From yourTable t2 where t1.Country = t2.Country)

Chirag

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-11 : 09:35:07

Hiya ,
Thx for the reply...I am a bit confused though..
I only have one table CCURRA...Not t1 & t2 ??
I also use a Distributed Query.

Using your example I have tried the following live code

SELECT *
FROM OPENQUERY (MOVEX_EXTRANET,
'select * FROM MVXADTASWC.CCURRA
where cucutd = (Select Max(cucutd) FROM MVXADTASWC.CCURRA where cucucd = cucucd) ')

When I run the query I only get one record result

where cucucd = 20050930 which is more than likely then newest date
but I Should get a row like this returned for each Country ?

Any more help please.

Ray..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:40:23

Try this. An advice though, if you are having trouble with aliases, take care when writing queries.

Select * From MVXADTASWC.CCURRA t1 where 
t1.cucutd = (Select Max(t2.cucutd) From MVXADTASWC.CCURRA t2 where t2.cucucd = t1.cucucd)

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-11 : 10:51:40

I wonder if you use Pofile to trace what an ODBC call to SQL server would show for FIRST() and LAST()...since the order of data in a database has no meaning, it would have to convert it to something like MIN or MAX....unless it converts it to something LIKE SELECT @x = Col, which for a multi-row result would return an single arbitrary column...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-09-11 : 11:42:46

Thx for all your help so far guys..I nearly have it..
Using the advice previously I have the following code to bring back the most recent Records per country.

SELECT *
FROM OPENQUERY (MOVEX_EXTRANET,
'Select * From MVXADTASWC.CCURRA t1 where
t1.cucutd = (Select Max(t2.cucutd) From MVXADTASWC.CCURRA t2 where t2.cucucd = t1.cucucd) AND CUCONO = 400 AND CUCRTP = 1 ' )

I get 17 records returned ( but access has 18 returned.)
After checking I can see that the EURO value is left out of the final result table..I can see why this is happening but need a way around it....

This is happening because of the data that relates to the Euro.
In my DB I have the following records where country is Eur

CUCONO CUCUCD CUCRTP CUCUTD CUARAT
400 EUR 1 20010101 1
402 EUR 1 20010101 1
500 EUR 1 20050101 1.45

If I use the max code as advised then CUCONO 500 is seen as the most recent exchange rate as its date is in 2005 but I wish to filter on CUCONO=400. If I add CUCONO=400 before I use the max code I STILL do not get a Euro value back ??

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-11 : 14:44:51

I did the Profiler thing and watch Access execute...I did both Max and Fisrt...first does nothing and returns all the rows...access must take the first one in the result set


SQL:BatchCompleted	SELECT "OrderID"  FROM "dbo"."Orders" 	Microsoft® Access		sa	0	21	0	30	2248	58	2006-09-11 14:39:43.890	
SQL:BatchStarting	SELECT MAX("OrderID" )  FROM "dbo"."Orders" 	Microsoft® Access		sa					2248	58	2006-09-11 14:40:28.967

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 15:02:39

One Euro row missing? Most probably because you use Euro for at least two countries.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources