query difficulties

Hello All,
I hope you can help with this.
I have next kind of data in the database(80000 rows) and I
want to drop out the min and max values.
day number prod quantity
monday 411 12004 545
monday 411 12004 776
monday 411 12004 345
monday 411 12004 876
monday 411 12004 764
tuesday 411 12004 324
tuesday 411 12004 225
tuesday 411 12004 421
tuesday 411 12004 314
tuesday 411 12004 432
I want to get rid off: monday 411 12004 345
monday 411 12004 876
tuesday 411 12004 432
tuesday 411 12004 225
and so on.
Thanks!help
CREATE TABLE #Test
(
col1 VARCHAR(10),
col2 INT,
col3 INT,
col4 INT
)
INSERT INTO #Test VALUES ('monday',1,12004,500)
INSERT INTO #Test VALUES ('monday',1,12004,200)
INSERT INTO #Test VALUES ('monday',1,12004,2)
INSERT INTO #Test VALUES ('tuesday',1,12004,50)
INSERT INTO #Test VALUES ('tuesday',1,12004,10)
INSERT INTO #Test VALUES ('tuesday',1,12004,100)
SELECT * FROM #Test
WHERE col4 =(SELECT MAX(col4)FROM #Test t
WHERE t.col1=#Test.col1)
OR col4=(SELECT MIN(col4)FROM #Test t
WHERE t.col1=#Test.col1)
ORDER BY col1
"help" <anonymous@.discussions.microsoft.com> wrote in message
news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||Try,
use northwind
go
create table t (
[day] varchar(10),
number int,
prod int,
quantity int)
go
insert into t values('monday', 411, 12004, 545)
insert into t values('monday', 411, 12004, 776)
insert into t values('monday', 411, 12004, 345)
insert into t values('monday', 411, 12004, 876)
insert into t values('monday', 411, 12004, 764)
insert into t values('tuesday', 411, 12004, 324)
insert into t values('tuesday', 411, 12004, 225)
insert into t values('tuesday', 411, 12004, 421)
insert into t values('tuesday', 411, 12004, 314)
insert into t values('tuesday', 411, 12004, 432)
go
delete t
where
not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity < t.quantity)
or not exists (select * from t as a where a.[day] = t.[day] and a.number =
t.number and a.prod = t.prod and a.quantity > t.quantity)
go
select * from t
go
drop table t
go
AMB
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>|||I understand that you want to delete rows having the minimum and
maximum values of Quantity for each combination of Day, Number and
Prod. Try:
DELETE FROM YourTable
WHERE quantity IN
((SELECT MIN(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod),
(SELECT MAX(quantity)
FROM YourTable AS T
WHERE day = YourTable.day
AND number = YourTable.number
AND prod = YourTable.prod))
Make sure you have a current backup and test this out before you delete
any actual data.
David Portas
SQL Server MVP
--|||Just curious what does the # sign mean in #test?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> help
> CREATE TABLE #Test
> (
> col1 VARCHAR(10),
> col2 INT,
> col3 INT,
> col4 INT
> )
> INSERT INTO #Test VALUES ('monday',1,12004,500)
> INSERT INTO #Test VALUES ('monday',1,12004,200)
> INSERT INTO #Test VALUES ('monday',1,12004,2)
> INSERT INTO #Test VALUES ('tuesday',1,12004,50)
> INSERT INTO #Test VALUES ('tuesday',1,12004,10)
> INSERT INTO #Test VALUES ('tuesday',1,12004,100)
>
> SELECT * FROM #Test
> WHERE col4 =(SELECT MAX(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> OR col4=(SELECT MIN(col4)FROM #Test t
> WHERE t.col1=#Test.col1)
> ORDER BY col1
>
>
> "help" <anonymous@.discussions.microsoft.com> wrote in message
> news:041201c539dc$3f13d5a0$a601280a@.phx.gbl...
>|||Aaron
#--local temporary table
##--global temporary table
For more details please refer to the BOL
"Aaron" <kuya789@.yahoo.com> wrote in message
news:%23C3sxFeOFHA.3444@.tk2msftngp13.phx.gbl...
> Just curious what does the # sign mean in #test?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePwR61dOFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||Hi,
Try this :
delete t
where quantity in (select min(quantity) from t group by [day], number, prod)
or quantity in (select max(quantity) from t group by [day], number, prod)
"help" wrote:

> Hello All,
> I hope you can help with this.
> I have next kind of data in the database(80000 rows) and I
> want to drop out the min and max values.
> day number prod quantity
> monday 411 12004 545
> monday 411 12004 776
> monday 411 12004 345
> monday 411 12004 876
> monday 411 12004 764
> tuesday 411 12004 324
> tuesday 411 12004 225
> tuesday 411 12004 421
> tuesday 411 12004 314
> tuesday 411 12004 432
> I want to get rid off: monday 411 12004 345
> monday 411 12004 876
> tuesday 411 12004 432
> tuesday 411 12004 225
> and so on.
> Thanks!
>