CODEWITHSUNDEEP
sqlsql-server
filifunk
filifunk

Reputation: 837

Trying "over partition by" and its not working

This works:

select top 100 cti.tradingitemid, pe.pricingDate, pe.priceClose, pe.adjustmentFactor, cc.countryId, cc.companyId
from ciqcompany cc
join ciqfinperiod cfp on cfp.companyid=cc.companyid
join ciqfininstance cfi on cfi.financialperiodid=cfp.financialperiodid
join ciqsecurity cs on cs.companyid = cc.companyid
join ciqtradingitem cti on cti.securityid=cs.securityid 
join ciqPriceEquity pe on pe.tradingItemId=cti.tradingItemId
where pe.pricingDate >= '1974-12-31 00:00:00.000' and pe.pricingDate <'1975-02-01 00:00:00.000' and cc.countryId = 213
order by cc.companyId

The problem is that I get hundreds of lines for the same companyID. I just want one per companyID, the first one would work fine.

I've been looking around and this is the best I could come up with:

select *
FROM ( 
        Select  cti.tradingitemid, 
                pe.pricingDate, 
                pe.priceClose, 
                pe.adjustmentFactor, 
                cc.countryId
                Row_number() OVER (PARTITION BY cc.companyId ORDER BY pe.pricingDate) RN
        from ciqcompany cc
                join ciqfinperiod cfp on cfp.companyid=cc.companyid
                join ciqfininstance cfi on cfi.financialperiodid=cfp.financialperiodid
                join ciqsecurity cs on cs.companyid = cc.companyid
                join ciqtradingitem cti on cti.securityid=cs.securityid 
                join ciqPriceEquity pe on pe.tradingItemId=cti.tradingItemId
        where pe.pricingDate >= '1974-12-31 00:00:00.000' and pe.pricingDate <'1975-02-01 00:00:00.000' and cc.countryId = 213
        order by cc.companyId
) 
Where rn = 1

The syntax at Row_number() is throwing it off, but to me it looks like other examples I see.

Any ideas would be much appreciated.

Upvotes: 0

Views: 277

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Three things:

  • Your subquery needs a table alias.
  • The subquery cannot contain order by.
  • And you need a comma before row_number().

So:

select t.*
from ( Select  cti.tradingitemid, 
                pe.pricingDate, 
                pe.priceClose, 
                pe.adjustmentFactor, 
                cc.countryId,
                Row_number() OVER(PARTITION BY cc.companyId ORDER BY pe.pricingDate) RN
        from ciqcompany cc
                join ciqfinperiod cfp on cfp.companyid=cc.companyid
                join ciqfininstance cfi on cfi.financialperiodid=cfp.financialperiodid
                join ciqsecurity cs on cs.companyid = cc.companyid
                join ciqtradingitem cti on cti.securityid=cs.securityid 
                join ciqPriceEquity pe on pe.tradingItemId=cti.tradingItemId
        where pe.pricingDate >= '1974-12-31' and pe.pricingDate < '1975-02-01' and
              cc.countryId = 213
       ) t
order by cc.companyId;

I removed the time component from the dates. It is not needed for the logic.

Upvotes: 0

K4M
K4M

Reputation: 952

Would this work?

SELECT *
FROM (
 .....
 ---- missing comma after cc.countryId
 ---- remove order by 
) A
WHERE
   A.RN = 1
ORDER BY cc.companyId

Upvotes: 1

Related Questions