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
 General SQL Server Forums
 New to SQL Server Programming
 distinct in sql

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 wrong
select 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??
Go to Top of Page

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
Go to Top of Page

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 data

Cheers
MIK
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 06:29:07
[code]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' )[/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
Go to Top of Page

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
Go to Top of Page

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,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
Go to Top of Page

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,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
Go to Top of Page
   

- Advertisement -