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.
Author |
Topic |
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-07 : 05:47:53
|
hi im trying to select all columns from a table but on 2 of the columns i need a distinct so it only pull one date and sharecureency from all the data in table.was trying it something like this but i no its wrongselect PK_DailyPrice, FK_MasterInventory, FK_Fund, distinct[date], ISIN, FundName,distinct [shareCurrency]from generic.DailyPrice any help would be great |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-07 : 06:02:18
|
what if two diffrent Fk_masterinventory have same currency?? |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-07 : 06:04:37
|
if it does i only take the 1 as the price per share number will be the same so i only want it once |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-07 : 06:19:28
|
Provide sample data in the form of INSERT statements and the desired out in light of the sample dataCheersMIK |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-07 : 06:29:07
|
[code]INSERT INTO generic.dailypriceVALUES ('4484284' '1671' '248' '2013-02-22 00:00:00.000' 'IE00B296WL75' 'CALAMOS GLOBAL FUNDS PLC' 'USD' '14.820000000' '0.000000000' '3292559.970000' '1.000000000' '4484285' '1663' '248' '2013-02-22 00:00:00.000' 'IE00B296WH30' 'CALAMOS GLOBAL FUNDS PLC' 'USD' '15.160000000' '0.000000000' '12375274.100000' '1.000000000' '4484286' '1721' '249' '2013-02-22 00:00:00.000' 'IE00B6VD6678' 'CALAMOS GLOBAL FUNDS PLC' 'USD' '15.960000000' '0.000000000' '16306.160000' '1.000000000' '4484287' '1735' '249' '2013-02-22 00:00:00.000' 'IE00B7L9LJ07' 'CALAMOS GLOBAL FUNDS PLC' 'USD' '15.960000000' '0.000000000' '16341.130000' '1.000000000' )[/code]so the last 2 in list have same date currency and pprice so all i need is one o thm with the other three |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-07 : 06:31:40
|
can it be done to have 2 columns as distinct and the rest not |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-07 : 06:48:37
|
Converting your statements in generalized scenario. Perhaps there is data in your table having columns A,B,C and D with following data.A,B,C,D1,2,1,13,4,1,15,6,1,1And you want to have that the data for A and B appears as it it, BUT for, C and D just once? If so, then this is not possible.. Because SQL quries returns two dimensional information. Btw why are you wanting to pull information in this way? are you trying to display C and D fields somewhere on a front page? and since they have duplicated values so you want them be distinct?CheersMIK |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-07 : 07:54:01
|
this might explain it better a) For each daily price per share , get unique list of currencies,dateb) For each currency in the list get exchange rate for each day in the daily price per share files,once i get that i have to update them values to another table but i need more than just those three columns as u see belowc) Update exchange rate table witha.CMONREF with FUND_CURRENCYb. CODMON with SHARE_CLASS_CURRENCYc. TCOURS with ROUND(1/EXCHANGE_RATE,6)d. DCTA with NAV_DATEe. DCTA_TCH with NAV_DATE |
|
|
|
|
|
|
|