Eliminating top 1% of records from a temp table

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

SQL Server 2005 Forums

Transact-SQL (2005)

Eliminating top 1% of records from a temp table

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 15:55:12

Hi,

I have a #temp table and I would like to eliminate the top 1% of data from the table based on a column.

If the column I need to validate against is called LevelDiff, what filter should I use toe delete these records?

for example if I have the temp table populated as below:

--Create #Temp table
SELECT
InquiryDate,
Client,
InquiryID,
DealerCount,
ResponseCount,
ExecutedDealer,
ExecutedLevel = CPLevel,
CoverDealer,
CoverLevel,
LevelDiff = abs(CPLevel - CoverLevel),
TradeYear,
TradeMonthName,
TradeMonth,
DealerTypeDate,
QuarterByYear
into #Temp
FROM
#Response
WHERE
CoverLevel is not NULL
ORDER BY
InquiryDate,
InquiryID

--Before taking the average exclude the top 1% of the trades with --the widest spread between COVER and DONE
--LevelDiff = abs(CPLevel - CoverLevel)
--select LevelDiff from #Temp
select * from #Temp
where LevelDiff= 1/100*(LevelDiff)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 15:59:57

use
SELECT TOP 99 PERCENT columns...
FROM Table
ORDER BY yourcol DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 16:28:01

I forgot to add :

If I am selecting from the #Temp table that has the column in question, does it make sense to use the query you provided and insert into another temp table say #TempExclude before I calculate the average?

SELECT
TOP 99 PERCENT columns...
FROM #Temp
INTO
#TempExclude
ORDER BY yourcol DESC

--calculates the average
select
TradeMonthName,
TradeYear,
ExecuteToCoverDiff = avg(LevelDiff),
ResponseCount = avg(convert(float, ResponseCount)),
TradeMonth,
QuarterByYear

INTO #OldDealer
from
#Temp
WHERE DealerTypeDate = 'OLD'
group by
TradeYear,
QuarterByYear,
TradeMonthName,
TradeMonth

order by
TradeMonth,
TradeYear

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 16:42:32

I tried doing the following:

SELECT
TOP 99 PERCENT
InquiryDate,
Client,
InquiryID,
DealerCount,
ResponseCount,
ExecutedDealer,
ExecutedLevel,
CoverDealer,
CoverLevel,
LevelDiff
TradeYear,
TradeMonthName,
TradeMonth,
DealerTypeDate,
QuarterByYear
FROM #Temp
ORDER BY LevelDiff DESC

I get the below results which appears same as

select * from #Temp.

The query you suggested should exclude top 1% but the rowcount appears different.

select * form #Temp gives 12207 rows while
the query you suggested gives 12085 rows

Should I not be seeing the top 1% values for column Level Diff ?

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 16:59:47

I tried the below which definitely excludes the TOP 1% but I see column name LevelDiff as TradeYear column name.

The values are perfect though. I am missing the TradeYear column values .

Not sure why. Any idea?

SELECT
InquiryDate,
Client,
InquiryID,
DealerCount,
ResponseCount,
ExecutedDealer,
ExecutedLevel,
CoverDealer,
CoverLevel,
LevelDiff
TradeYear,
TradeMonthName,
TradeMonth,
DealerTypeDate,
QuarterByYear
FROM
#Temp
WHERE
LevelDiff NOT IN(SELECT
TOP 1 PERCENT t.LevelDiff
FROM #Temp t
ORDER BY LevelDiff DESC)
ORDER BY LevelDiff DESC

Thanks

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 17:12:51

Never mind, i was missing a comma.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 02:05:50

so were you able to sort it out?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources