select with subquery in select clause - 95922

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

-1
jrchaveztj
8/7/2010 5:10:47 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
735 Views

Similar Articles

[PageSpeed] 7

jrchaveztj (u63019@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@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

-3
Erland
8/7/2010 6:57:46 PM
Reply:

Similar Artilces:

show all price list in HTML bar for each selected item
In RMS client would like to see all the price lists appear when they select an item in the POS screen. MBS says this could probably be done in the HTML status bar but I don't know how to do that. Has anyone else had such a request or done something like this? Thanks ...

Select Case and Tabular Forms
It's a little complicated to explain what I want my end result to be, but right now, I have a tabular form with a field that I want to use to update an unbound text box, I've named Ability. In doing some tests to see if this would work, I tried writing a really basic sequence: Select Case [Ability ID] Case 1 Ability = 1 Case 2 Ability = 2 Case 3 Ability = 3 Case 4 Ability = 4 Case 5 Ability = 5 Case 6 Ability = 6 End Select The problem is, when I run this Select...Case statement, it only pulls the number from the...

How to select fields using either query criteria or a macro
I have created a database which logs operational data for an industrial water plant. I want to output a table of the data, based on inputs from the user. I've created a form where the user can enter a date range for the data they want to view, and a series of check boxes which correspond with the fields in the underlying table. In order to restrict the output data to the date range and fields required by the database user, I have created a query. My problem is that I can't seem to find a way to link the check boxes on my form to the 'show' checkbox in the query. ...

Select sheets from an array for printing
I need Help!! I have a workbook with hidden sheets, I want to search the visible sheets for those with value greater than 0 in C2 then print them. Can someone show me where I am going wrong with this code? Tahnks Sub PrintSelectedSheets() 'will print sheet with a value >0 in C2 For Each Sheet In ThisArray.Sheets(Array("Sheet1", "Sheet3", "Sheet5", "Sheet7", "Sheet9")) Sheet.Activate If Range("C2").Value > 0 Then ActiveSheet.PrintPreview End If Next End Sub Dim Sheet As Worksheet Dim shStyle As L...

Selective Out of Office replies
We're running Outlook 2003 against an Exchange 2000 server (soon to be Exchange 2003). I have a "normal" e-mail account - myname@mycompany.com and an alias on that account which we use on our web site - info@mycompany.com. If I turn on Out Of Office, and someone sends a message to info@mycompany.com, they get my auto-reply from myname@mycompany.com . Is there a way to configure the auto-reply for Out of Office to only send it back to people who mailed myname@ and not info@? If not, I assume the only option is to set up a separate e-mail account just for info@. TMN <tmn313@h...

Can I change Headers and footers in selected Reports at the same .
maybe... If you're writing about worksheets in the same workbook, you can group the sheets (click on the first tab and ctrl-click on subsequent tabs). Then File|page setup and change the headers/footers. Remember to ungroup the sheets (just rightclick on any of the grouped sheets and select ungroup sheets) or you may damage your workbook. Almost anything you do to a one sheet (of a group) is done to the rest of the sheets in that group. Tester wrote: -- Dave Peterson Thanks Dave, but no, I need to change headers and footers in over a hundred different folders on a regular bas...

No free/busy for select users after running DST Tool (NonExistent.
We ran the Exchange tool this weekend on our Exchange environment and it seems that a few people are showing no info for the free/busy calendar. These people were also in the .txt file named NonExistent.txt after running the tool. How can I fix this? On Tue, 6 Mar 2007 05:44:15 -0800, mpallo <mpallo@discussions.microsoft.com> wrote: >We ran the Exchange tool this weekend on our Exchange environment and it >seems that a few people are showing no info for the free/busy calendar. > >These people were also in the .txt file named NonExistent.txt after running >the too...

Select based on 1st character
HI, I have a query that finds duplicates based on [firstname] and [lastname] which works fine however what we have discovered is that if the name has a variation i.e. Rob instead of Robert it wont detect it. I would like to build an If statement so that if the 1st character (or 1st few characters) are alike to bring them into the data. Does anyone know how to build this? Post the SQL statement that you're using right now; we can show how to modify it using non-equi-joins. -- Ken Snell <MS ACCESS MVP> "dc" <dc@discussions.microsoft.com> wrote in m...

MMC 3.0 Auto Select ScopeNode
Hi, I'm trying to Auto Select a ScopeNode from a list of children. Unfortunately I could not find any way of doing so. I am developing my MMC app using Visual Studio 2008 and MMC 3.0. I have read on the internet about IConsole2 object that has the SelectScopeItem. However this object is not available in MMC 3.0. I was wondering if anybody has come across this problem and can share their knowledge. Thanks. -- Cheers, David ...

Selecting and then sorting a range
Hey, I had a problem that maybe someone can help me with. I'm trying to sort a range in a macro. The columns that compose the range never change, but the rows will. (i.e. columns a:o, but rows may vary from 26:400 or 26:40) I was trying to use range(selection, selection.end(xlDown)) and range(selection, selection.end(xlToRight)) The selection.end(xlDown) works fine, but I have a partially blank column in the middle of my column range. It has data for some jobs, but not all. When I do selection.end(xlTo Right), it stops at this blank. There is however, data I need sorted on the right of ...

Select Query With Parents And Child's In one row
I have one table called "ParentList" like this: ParentID, FirstName, LastName, Address, 1001 John Duo 123 Main St. 1002 Cris Taylor 456 Maple St. then I have a table called "Children" Like this: ChildID, ParentID, ChildFirst, 1001 1001 Ab 1002 1001 Bryan 1003 1001 Dave 1004 1002 Jack 1005 1002 Bill So I want the sql results Like this: ParentID, FirstName, LastName, Address, Child(1), Child(2), Child(3), 1001 John Duo 123 Ma...

"Select signatures for account:" Causes Outlook 2003 to Crash
When I go into Tools > Options > Mail Format > Signatures > Select signatures for account: and select a different account from the drop-down, Outlook crashes. Anyone else have this problem? Anyone been able to fix it? Something is corrupt. I'd start with Help, Detect and Repair, unless it only crashes after you select the new sig - then delete the sig files and make a new one. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@li...

Excel 97
I have 2 different pivot tables, each with a total. If I select both totals using Office 2003, the SUM of the 2 cells appears in the bottom right of the spreadsheet. If I try to select the same 2 cells using Office 97, nothing happens and the cells are not selected. Can anyone confirm whether or not this function is only available with Office 2000 or later? ...

Passing a variable to a select query.
Hi all, I am trying to select cmr from Customer table and pass this variable [Cust] to a query. When I open the form to select the customer it gets copied into the variable. So far no problem. When the OpenQuery executes, it asks for the parameter again. Class module linked to my forms option button : Private Sub Option2_Click() Dim Cust Set Cust = Cmr DoCmd.OpenQuery "CustBal", acNormal, acEdit End Sub Custbal query : PARAMETERS Cust Text; SELECT TbTrans1.Tranid, TbTrans1.Cmr, TbTrans1.date, TbTrans1.Amount FROM TbTrans1 WHERE Tbtrans1.Cmr LIKE '*' & [Cust] & ...

Selecting category from email
Hi, Is there any way to select a catagory of contacts to e-mail while in the mail message form (without having to go to the contacts section)? thanks for the help, Dan Irwin nope. -- Judy Gleeson, MVP Outlook Acorn Training and Consulting www.acorntraining.com.au Everyone - turn on your Advanced Toolbars and learn how to use the Field Chooser and Group by Box!! <harryguy082589@gmail.com> wrote in message news:1147667940.048002.27600@j73g2000cwa.googlegroups.com... > Hi, > > Is there any way to select a catagory of contacts to e-mail while in > the mail message f...

Lookup table with click-and-select?
Can someone point me in the right direction? I have several thermos bottles of various sizes that I use for tea. I have several varieties of tea that require different brewing parameters. I'd like to create a worksheet that will generate "brew slips" -- little slips of paper that show how much tea to use for each thermos. The left side of the sheet will be the brew sheet. It will have columns for ounces, cups, milliliters, grams of tea, etc. Something like this: Thermos Cups Oz mL g/pot A 2.0 16 473 ?? B 3.0 24 710 ?? C...

Dynamic Column Selection in Formula
Hi I have a simple function as below which selects a range from last years Workbook: =SUM('C:\Documents and Settings\username\Desktop\[Workbook05.xls]Invoices'!B4:I4) Each month the Second column is updated: i.e. next month I4 will become J4. Instead of updating the formula I want to be able to update another Cell with the text "I" to "J". E.G. cell A1 contains "I", refer to this cell in the formula B4:I4 rather than hardcoding it. Any advice would be much appreciated. Thanks B Try =SUM(INDIRECT("'C:\Documents and Settings\username\Des...

Select Checks Generates 0 Balance Payments
GP 8.0, SQL When I run the select checks process, it generates $0 balance payments transactions. Every time I run the process I edit checks and delete the $0 balance transactions. Why would select checks be generating these payments? How can I fix this so it doesn't happen any more? THanks, Jason ------=_NextPart_0001_A99BF2A7 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Jason, I understand you are using the Select Checks window to create your batch however you find zero dollar checks in the batch when you go to edit the payments. This is caused by either a manual...

Can't select objects in Excel 2007
I'm trying to create a form in Excel 2007, and although I can manipulate, copy & paste controls (check boxes, etc) I can't select multiple objects to align them, etc. I've checked on-line help (and have followed the instructions), and other websites, but no one seems to have indicated a problem. It is extremely cumbersome and I need a solution. Any ideas? Thanks in advance, Jim Berglund I know this is about a year too late, but it may help others who have stumbled across this page looking for similar help. On the Home tab, in the Editing group, click Find & Select....

Calendar Selection
I would like to have a cell validation with a calendar function. You've seen it; someone clicks on cell and instead of getting a dropdown list, you have a calendar you can select from with month and years to change from. Once the user selects the appropriate date, it places that date in the cell. Can this be done?? Hi DNA, See: http://www.rondebruin.nl/calendar.htm --- Regards, Norman "DNA" <dnoel@fsgbank.com> wrote in message news:8c29b822e21276273f63a3391d963f5b@localhost.talkaboutsoftware.com... >I would like to have a cell validation with a calend...

Wrong Combobox Selection
Problem: After selecting item 4 in a combobox using CB_SETCURSEL, the first item is always displayed as the default. I am filling a combobox with ~33 strings. The default selection I want is item 4 (nIdx = 3). I call ::SendMessage( hWnd, CB_SETCURSEL, nIdx, 0 ) to select item 4. Debugging code in OnInitDialog() AFTER the above call, using CB_GETCURSEL, returns 3, yet the combobox displays item 0 as the default. To further complicate things, on a different property page I have 2 comboboxes that use the same filling and default setting code. One displays item 0 as the default an...

How do I print selected rows?
I want to select some rows from two different worksheet pages and have them print out on the same piece of paper ( I get one row printed out per sheet of paper right now?) I want all selected rows to print out on the same piece of paper. Thanks -- kmf On a temporary basis you could open a third worksheet and copy and paste the required data. If you want this on a more permanent basis you could open a third worksheet and pull the required data in using a formula. Regards. Bill Ridgeway Computer Solutions "kmf" <kmf@discussions.microsoft.com> wrote in message news:AF3...

Parameter query to select multiple child records on the same field
I have a parent-child relationship where I want to be able to select all the children of the parent which meet multiple criteria on one field. For example, for a parent name of "Big Creek" I want to be able to select all the child records in which the map field contains "Dunsmore" AND those in which the map field contains "Cruso". I have no trouble building a query which works for one child paramter value, "Cruso" for example, but the multiple parameters on the same field have got me stumped. Thanks, Dave On Sat, 09 Feb 2008 16:09:37 -0500, David Wetm...

Ask a user to select a range in Excel
Hi, All: Can anyone tell me how to write a macro that asks a user to select a range in excel? then I would like to use values in the selectionfor other calculation later on. Thanks Ming, Try something like the following: Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox(prompt:="Select a range", Type:=8) If Rng Is Nothing Then MsgBox "no range selected" Else MsgBox "You selected: " & Rng.Address End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com ...

Select only one top row from each matching child
Hello, We are moving our project to MSSQL 2005, and I was wondering if there is more efficient way to do this: There is a parent table (think customers) and child table (think event log: evtID, evt_cstID, evtTime, evtAllTheRestOfFields). Application needs to retrieve a list of customers with the most recent event. Of course, I'd like to avoid making separate queries (in application code) to retrieve the TOP 1 ORDER BY evtTime DESC for each customer row. Until now we have been doing this: SELECT * FROM (SELECT *, (SELECT TOP 1 evtID FROM Events WHERE evt_cstID=cstID O...