| Author |
 Topic  |
|
|
rjhe22
Posting Yak Master
151 Posts |
 Posted - 03/07/2013 : 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 wrong
select PK_DailyPrice,
FK_MasterInventory,
FK_Fund,
distinct[date],
ISIN,
FundName,
distinct [shareCurrency]
from generic.DailyPrice
any help would be great |
|
|
ahmeds08
Constraint Violating Yak Guru
India
430 Posts |
Posted - 03/07/2013 : 06:02:18
|
| what if two diffrent Fk_masterinventory have same currency?? |
 |
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/07/2013 : 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
Aged Yak Warrior
Pakistan
826 Posts |
Posted - 03/07/2013 : 06:19:28
|
Provide sample data in the form of INSERT statements and the desired out in light of the sample data
Cheers
MIK |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/07/2013 : 06:29:07
|
INSERT INTO generic.dailyprice
VALUES ('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' )
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
Posting Yak Master
151 Posts |
Posted - 03/07/2013 : 06:31:40
|
| can it be done to have 2 columns as distinct and the rest not |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
826 Posts |
Posted - 03/07/2013 : 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,D
1,2,1,1
3,4,1,1
5,6,1,1
And 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?
Cheers
MIK |
|
|
|
rjhe22
Posting Yak Master
151 Posts |
Posted - 03/07/2013 : 07:54:01
|
this might explain it better
a) For each daily price per share , get unique list of currencies,date
b) 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 below
c) Update exchange rate table with
a.
CMONREF with FUND_CURRENCY
b. CODMON with SHARE_CLASS_CURRENCY
c. TCOURS with ROUND(1/EXCHANGE_RATE,6)
d. DCTA with NAV_DATE
e. DCTA_TCH with NAV_DATE |
|
|
| |
Topic |
|
distinct in sql
