Removing max value and min value from a list

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

Removing max value and min value from a list

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-03-03 : 09:15:00

Hi everyone,

So I am trying to find the min and max value in a table. After I have found the min and max value for each customer in a table, I then want to remove the min and max from the table with out deleting them from the table. And then I want to total the customers total transactions(excluding the min and max values).

So far I have done a query to find out the min and max


select distinct cardnumber,min(DailyTransactionValue) MinSpend
from tests where DailyTransactionValue > (select min(DailyTransactionValue)from tests)
				    


select distinct cardnumber,max(DailyTransactionValue) 
		from tests 
		where DailyTransactionValue < (select max(DailyTransactionValue)from tests)

I then want to remove all customers who do not have the same cardnumber and value from the min and max query and place it in a separate table. In other I want to remove the min value and the max value.

A customer may have 8 transactions, I dont want to remove the customer completely from the list so instead of having 8 transactions they would have only 6 transactions. Of course if the customer only had 2 transactions then this would remove them all together.

Any ideas......

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-03 : 09:22:22

try this,

select 
	* 
from 
	tests B
join 
	(
	select 
		cardnumber,
		min(DailyTransactionValue) MinSpend,
		max(DailyTransactionValue) MaxSpend
	from 
		tests  
	group by 
		cardnumber			    
	)A
	on B.cardnumber=A.cardnumber and B.DailyTransactionValue<>A.MinSpend and B.DailyTransactionValue<>A.MaxSpend

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:24:58

[code]
SELECT t.*
FROM tests t
INNER JOIN (SELECT cardnumber,
MIN(DailyTransactionValue) AS MinSpend,
MAX(DailyTransactionValue) AS MaxSpend
FROM tests
GROUP BY cardnumber) t1
ON t1.cardnumber=t.cardnumber
WHERE (t.DailyTransactionValue-t1.MinSpend) * (t.DailyTransactionValue-t1.MaxSpend)<>0
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources