select with subquery in select clause
in [Server Configuration]
|
Prev: SQL Server 2008 R2 Native Client backward-compatible to SQL 2005/2008??
Next: select with subquery in select clause
From: jrchaveztj on 7 Aug 2010 13:10 Hi I need to make a select from 2 tables whit this layout always with the newest datetime exchange currency. The local cuurency is Mexican Pesos Table 1 Currencies example: CurrencyID Key Description 1 USD USA Dollar 2 EUR European Euro 3 Yen Japan Yen Tabla 2 is Day exchange Currency CurrencyID Equivalent DateTime 1 12.50 2010-08-07 08:00:00 1 12.55 2010-08-07 08:10:00 1 12.49 2010-08-07 08:20:00 1 12.60 2010-08-07 08:30:00 1 12.51 2010-08-07 08:40:00 1 12.52 2010-08-07 08:50:00 1 12.48 2010-08-07 09:00:00 1 12.61 2010-08-07 09:10:00 <-- the newest xchange for USD 2 16.87 2010-08-07 08:00:00 2 16.90 2010-08-07 08:10:00 2 16.88 2010-08-07 08:20:00 2 16.85 2010-08-07 08:30:00 2 16.87 2010-08-07 08:40:00 2 16.92 2010-08-07 08:50:00 2 16.80 2010-08-07 09:00:00 2 16.95 2010-08-07 09:10:00 <-- the newest xchange for Euro 3 6.73 2010-08-07 08:00:00 3 6.70 2010-08-07 08:10:00 3 6.78 2010-08-07 08:20:00 3 6.75 2010-08-07 08:30:00 3 6.77 2010-08-07 08:40:00 3 6.72 2010-08-07 08:50:00 3 6.70 2010-08-07 09:00:00 3 6.75 2010-08-07 09:10:00 <-- the newest xchange for Euro The select query I need is always the newest exchange currency for each currency in table 1, something like this CurrencyID Key Description Equivalent DateTime 1 USD USA Dollar 12.61 2010-08-07 09:10:00 2 EUR European Euro 16.95 2010-08-07 09:10:00 3 Yen Japan Yen 6.75 2010-08-07 09:10:00 How can i do the select? Plese help me Jose Roberto Chavez
From: Erland Sommarskog on 7 Aug 2010 14:57
jrchaveztj (u63019(a)uwe) writes: > Hi I need to make a select from 2 tables whit this layout always with the > newest datetime exchange currency. > The local cuurency is Mexican Pesos > > Table 1 Currencies example: > CurrencyID Key Description > 1 USD USA Dollar > 2 EUR European Euro > 3 Yen Japan Yen Personally, I think the currency codes are very good natural primary keys and there is little need for surrogates here. > > Tabla 2 is Day exchange Currency > CurrencyID Equivalent DateTime > 1 12.50 2010-08-07 08:00:00 > 1 12.55 2010-08-07 08:10:00 > 1 12.49 2010-08-07 08:20:00 > 1 12.60 2010-08-07 08:30:00 > 1 12.51 2010-08-07 08:40:00 > 1 12.52 2010-08-07 08:50:00 > 1 12.48 2010-08-07 09:00:00 > 1 12.61 2010-08-07 09:10:00 <-- the newest xchange for USD WITH numbered_fx AS ( SELECT CurrencyID, FXrate, rowno = row_number() OVER (PARTITION BY CurrencyID ORDER BY DateTime DESC) FROM fx_rates ) SELECT c.KEY, fx.FXrate FROM numbered_fx WHERE rowno = 1 This solution requires SQL 2005; in the future, please specify which version of SQL Server you are using. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |