RE: Top 35 1

[allyne]

Hi Everyone,

I asked this question earlier but it looks like I simplified it to much...so here we go again. I'm using SQL 7.0 and have a query that looks like this.

SELECT

Carrier = dbAdmin.dbo.tblMasterInOut.Carrier,

MarketBid =tblMarketByRegion.Bid,

MasterInOutBID = dbAdmin.dbo.tblMasterInOut.BID,

[60] = SUM(CASE WHEN FinancialReporting.dbo.Top3Current3060TEMP1.SETTLEMENT_CYCLE_ID = 3 AND InfoROAM2.dbo.cycle_summary.IN_OUT_COLLECT = 'O'
THEN InfoROAM2.dbo.cycle_summary.AIR_TIME
ELSE 0
END),

[30] = SUM(CASE WHEN FinancialReporting.dbo.Top3Current3060TEMP1.SETTLEMENT_CYCLE_ID = 2 AND InfoROAM2.dbo.cycle_summary.IN_OUT_COLLECT 'O'
THEN InfoROAM2.dbo.cycle_summary.AIR_TIME
ELSE 0
END),

[CURRENT] = SUM(CASE
WHEN FinancialReporting.dbo.Top3Current3060TEMP1.SETTLEMENT_CYCLE_ID = 1 AND InfoROAM2.dbo.cycle_summary.IN_OUT_COLLECT 'O'
THEN InfoROAM2.dbo.cycle_summary.AIR_TIME
ELSE 0
END),

INTO #Out

FROM dbAdmin.dbo.tblMasterInOut INNER JOIN InfoROAM2.dbo.cycle_summary ON
InfoROAM2.dbo.cycle_summary.ROAMING_BID = dbAdmin.dbo.tblMasterInOut.BID INNER JOIN
Top3Current3060TEMP1 ON Top3Current3060TEMP1.SETTLEMENT_CYCLE = InfoROAM2.dbo.cycle_summary.SETTLEMENT_CYCLE INNER JOIN
tblMarketByRegion ON InfoROAM2.dbo.cycle_summary.MEMBER_BID = tblMarketByRegion.BID

GROUP BY dbAdmin.dbo.tblMasterInOut.Carrier,tblMarketByRegion.Bid,dbAdmin.dbo.tblMasterInOut.BID

(SELECT Carrier,MarketBid,MasterInOutBID,[60],[30],[CURRENT]
FROM #Out
WHERE MasterInOutBID IN(SELECT TOP 35 MasterInOutBID
FROM #Out
GROUP BY Carrier,MarketBid,MasterInOutBID,[CURRENT]
ORDER BY MarketBid ASC,[Current]DESC))
ORDER BY MarketBid ASC,[Current] DESC

This gives me 35 records for each MarketBid....The results I want is the top 35 Current($ Amount) for each MarketBid. (MasterInOutBid is the only field that is unique)

Can someone let me know what I'm doing wrong. I feel like I'm making this alot harder than it should be.

Once again thanks for all your help!

 

[chiph]

TOP only limits the number of rows returned to you, it doesn't reorder them or restrict them in any way. Try the MAX() function to return the highest valued bidmarkets, and then get the TOP 35 of that select.

Chip H.
Error on line 9: Object of type SIGNATURE expected

 

[allyne]

Hi Chip,

Tried your suggestion but I still get the same results. Any other suggestions?

Thanks for your help!

 

[taasarge]

I have used this logic to get the top 10 or 35 results back. You would have to adjust your sql to fit...

SELECT *
FROM CONSUMER.VDATA A
WHERE 10 >
(SELECT COUNT (*)
FROM CONSUMER.VDATA B
WHERE A.DOLLARS < B.DOLLARS)
ORDER BY DOLLARS DESC

This can run slow if not tied to an index...

 

[tlbroadbent]

Assuming you have unserted the data into the #out table correctly, you should be able to extract the result you want with the following query.

SELECT
Carrier,
MarketBid,
MasterInOutBID,
[60], [30],
[CURRENT]
FROM #Out o
WHERE MasterInOutBID IN
(SELECT TOP 35 MasterInOutBID
FROM #Out Where MarketBid=o.MarketBid
ORDER BY [Current] DESC)
ORDER BY MarketBid ASC, [Current] DESC Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers.&quot;