Re: Query optimization

Tom/Plamen,

Now i am thinking that, i am hardcoding value 2 (no of rows to return) in
the query. I thought of including another INT column which would tell me how
many rows to return.

But my manager is asking me to use the existing parameter @param and not to
include another parameter.

ie.., if i need top 20 records i will pass @param =20
if i need bottom 20 i will pass @param= -20
if i need all records i will pass 0 or some other number. Effectively i will
pass the actual number of rows to be returned by SP in @param itself. If its
positive number then its TOP, if its negative then its BOTTOM.

I have no clue how to change this query to suit this revised requirment. Any
help would be greatly appreciated.

Regards
Pradeep

"Tom Cooper" wrote:

Another way would be

Select TOP (Case When @param = 3 Then 2147483647 Else 2 End) D.state as
state, sum(T.cost) as amount
From tblT T join tblD D on T.DEVID = D.DEVID
Group by state
Order by sum(t.cost) * Case When @param = 2 Then -1 Else 1 End desc, state;

But I agree with Plamen, separate queries will be more efficient. BTW, your
second query (the one to get the bottum two rows), can be written more
simply than your version. Just use

--query to fetch bottom 2 states
Select TOP 2 D.state as state, sum(T.cost) as amount
From tblT T join tblD D on T.DEVID = D.DEVID
Group by state
Order by sum(t.cost), state;

BTW, thanks for providing the create table and insert statements with sample
data. That always makes answering questions much easier.

Tom

"Plamen Ratchev" <Plamen@xxxxxxxxxxxxx> wrote in message
news:vZWdnVF-_pvDXYHXnZ2dnUVZ_tydnZ2d@xxxxxxxxxxxxxxxx
Here is one way to merge all 3 queries into one, but running as separate
queries will be more efficient:

SELECT state, amount
FROM (
SELECT D.state AS state,
SUM(T.cost) AS amount,
ROW_NUMBER() OVER(ORDER BY SUM(T.cost)) AS rk_asc,
ROW_NUMBER() OVER(ORDER BY SUM(T.cost) DESC) AS rk_desc
FROM tblT AS T
JOIN tblD AS D
ON T.devid = D.devid
GROUP BY state) AS G
WHERE CASE WHEN @param = 1 AND rk_desc <= 2
THEN 'True'
WHEN @param = 2 AND rk_asc <= 2
THEN 'True'
WHEN @param = 3
THEN 'True'
END = 'True'
ORDER BY CASE WHEN @param IN (1, 3) THEN amount END DESC,
CASE WHEN @param = 2 THEN amount END,
state;

--
Plamen Ratchev
http://www.SQLStudio.com


.

Relevant Pages

  • Re: Query optimization
    ... Select TOP ) D.state as state, sumas amount ... From tblT T join tblD D on T.DEVID = D.DEVID ... But my manager is asking me to use the existing parameter @param and not to ...
    (microsoft.public.sqlserver.programming)
  • Re: Query optimization
    ... Select TOP ) D.state as state, sumas amount ... But my manager is asking me to use the existing parameter @param and not to ... From tblT T join tblD D on T.DEVID = D.DEVID ...
    (microsoft.public.sqlserver.programming)
  • Re: Query optimization
    ... Won't creating any indexes for this query help? ... "Plamen Ratchev" wrote: ... SUMAS amount, ... ORDER BY CASE WHEN @param IN THEN amount END DESC, ...
    (microsoft.public.sqlserver.programming)
  • Re: Query optimization
    ... SUMAS amount, ... FROM tblT AS T ... JOIN tblD AS D ... ORDER BY CASE WHEN @param <= 0 THEN amount END DESC, ...
    (microsoft.public.sqlserver.programming)
  • Re: Total Box
    ... so the amount you are trying to add is not in the form's RecordSource ... and include Product.Amount in the query. ... > My form has lots of combo boxes but lets imagine it has two 'Processor' ... > The fields that contain the prices are in differing tables but the field ...
    (microsoft.public.access.forms)
Quantcast