How can I optmize this query

Hi,
How can I optmize this query
Select prodid,poid,po_date from po_sales
WHERE poid = 123 and po_sale <> (select
max(po_sale) from po_sales where poid = 123 ) and
po_sale <>(select min(po_sale) from po_sales where poid = 123)
ThanksHi
Just check this query:
Select prodid,poid,po_date from po_sales
FROM po_sales
INNER JOIN (Select poid from po_sales
WHERE poid = 123
GROUP BY poid
HAVING max(po_sale) <> po_sale AND
min(po_sale) <> po_sale) AS
Derive ON Derive.poid = po_sales.poid
is this the one that u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chris" wrote:

> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||
I would use a correlated query rather than named the Paramteres three times:
Select prodid,poid,po_date from po_sales AS po
WHERE poid = 123 and po_sale <(select
max(po_sale) from po_sales where poid = po.poid ) and
po_sale <>(select min(po_sale) from po_sales where poid = po.poid)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris" <Chris@.discussions.microsoft.com> schrieb im Newsbeitrag
news:762CD1A6-AD5C-4F57-837A-22E6788674EA@.microsoft.com...
> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales AS po
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = po.poid ) and
> po_sale <>(select min(po_sale) from po_sales where poid = po.poid)
> Thanks|||Chris
Untested
Select prodid,poid,po_date from po_sales
WHERE poid = 123 and po_sale not in
(
select max(po_sale)as po_sale from po_sales where poid = 123
union all
select max(po_sale) from po_sales where poid = 123
)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:762CD1A6-AD5C-4F57-837A-22E6788674EA@.microsoft.com...
> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||Depending on what's in the po_sales column, you could try this:
DROP TABLE #po_sales
CREATE TABLE #po_sales ( prodid INT, poid INT, po_sale INT, po_date DATETIME
)
INSERT INTO #po_sales SELECT 1, 123, 10, '1 Apr 2005'
INSERT INTO #po_sales SELECT 2, 123, 20, '2 Apr 2005'
INSERT INTO #po_sales SELECT 3, 123, 30, '3 Apr 2005'
INSERT INTO #po_sales SELECT 4, 123, 40, '4 Apr 2005'
-- SELECT * FROM #po_sales
-- Your query
SELECT prodid, poid, po_date
FROM #po_sales
WHERE poid = 123
AND po_sale <> ( SELECT MAX( po_sale ) FROM #po_sales WHERE poid = 123 )
AND po_sale <> ( SELECT MIN( po_sale ) FROM #po_sales WHERE poid = 123 )
-- My query
SELECT a.prodid, a.poid, a.po_date, a.po_sale
FROM #po_sales a
INNER JOIN #po_sales b ON a.poid = b.poid
WHERE a.poid = 123
GROUP BY a.prodid, a.poid, a.po_date, a.po_sale
HAVING a.po_sale > MIN( b.po_sale )
AND a.po_sale < MAX( b.po_sale )
Select them both in QA, and press Ctrl + L to compare the Execution plan
costs. Let me know how you get on.
Damien
"Chris" wrote:

> Hi,
> How can I optmize this query
>
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks|||Chris,
It is highly unlikely that you will get any measurable performance gain
by rewriting the query. Optimizing the indexes for this query will help
(if you haven't done so already).
The query will benefit from an (nonclustered) index on
po_sales(poid,po_sale)
HTH,
Gert-Jan
Chris wrote:
> Hi,
> How can I optmize this query
> Select prodid,poid,po_date from po_sales
> WHERE poid = 123 and po_sale <> (select
> max(po_sale) from po_sales where poid = 123 ) and
> po_sale <>(select min(po_sale) from po_sales where poid = 123)
> Thanks