SQL server questions: max

Showing posts with label max. Show all posts

Tuesday, March 20, 2012

a too long query MAX(CASE WHEN

Hello

I am using an allready Full database MS SQL 2000

my 3 tables -->

Report :
ReportID (PK)
RName
RValue

Product :
PName
Category
ReportID (FK)

Infos :
IComments
IVaLue

my query (to get a new table with only columns, or a .NETcollection) -->

SELECT

Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,

Infos.Commentar AS IC,

MAX(CASE WHEN Product.Category = 50 THEN Product.PName END) AS P50,
MAX(CASE WHEN Product.Category = 54 THEN Product.PName END) AS P54,
MAX(CASE WHEN Product.Category = 78 THEN Product.PName END) AS P78,
MAX(CASE WHEN Product.Category = 540 THEN Product.PName END) AS P540,
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421

FROM

Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue

WHERE (Report.ReportID = 10)

GROUP BY Report.ReportID, Report.RName, Report.RValue, Infos.IComments

Report.ReportID = Product.ReportID --> Primary Key to Foreign Key
Report.RValue = Infos.IValue --> only on full text (100 char)

they are not indexed

in Product can be a few million of lines, a few 10.000 in Report, about 1000 in Infos

it can be very long
how can i do it in a better way ? (of course I cannot change the structure of tables, another aplication is using it)

thank youHi

This is some sort of odd pivot but I don't understand what your problem is or what you would like to accomplish. Please could you elaborate?|||on 3 tables i have columns or rows , i want to get only columns
MAX(CASE WHEN Product.Category = 1421 THEN Product.PName END) AS P1421 makes a column from a row|||It does indeed. So what is the problem? :)

EDIT - oh hang on - do you mean you want zero rows??|||i want to find a better way if exists|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH|||you think a union will be really faster ?|||you think a union will be really faster ?I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.|||ok i try it

thank you|||I think it may be faster - there are circumstances where UNIONS can be quicker than a "single" statement. It is just a suggestion... it removes the processing of aggregates so may improve performance there.

If the seperate queries would not return duplicate results (or you don't care if it does) you could speed it up further by using UNION ALL.

Just FYI,
Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries.
The data types must be compatible|||You could try a UNION and see if that works any better.

SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
Product.PName AS P50,
'' AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 50
UNION
SELECT Report.ReportID AS RID,
Report.RName AS RN,
Report.RValue AS RV,
Infos.Commentar AS IC,
'' AS P50,
Product.PName AS P54,
'' AS P78,
'' AS P540,
''AS P1421
FROM
Report INNER JOIN Product ON Report.ReportID = Product.ReportID
LEFT OUTER JOIN Infos ON Report.RValue = Infos.IValue
WHERE Category = 54
....
....
??
Also - your original query you posted is not what you are using since it is syntactically incorrect. You might want to consider some indexing too.

HTH

UNION ALL if no dupes.|||As mentioned in my next post :)

Thursday, March 8, 2012

a simple group by query, plz help

hi i want to get the result like
SELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1

but it doesn't allow me as it says
Server: Msg 8118, Level 16, State 1, Line 1
Column 't_bid.uId' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 't_bid.bidDate' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

i tried some combos for grp by like i used both uId and bidDate in grp by but it gave me multiple rows where as i only want one record with max bidPrice and auctionid=1 or anyother
plz helpSELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1
GROUP BY uId,bidDate

EDIT: What do you mean "multiple" rows?|||i meant multiple records, i have total of 2 records with auctionid=1 if i write this query as
SELECT MAX(bidPrice) as bidPrice,uId,bidDate FROM t_bid where auctionId=1 group by uId,bidDate
then it gives me both results instead of one|||max(colname) will give u the max value of that column in that table.

what u wanna do is find the max bidPrice for a a particular uID and bidDate rite?|||But they're not "duplicates"

They have different values. Uid may have different bid dates..which bid date do you want? MIN, MAX? You need to pick 1, or eliminate from the grouping...|||i want to get the maxprice, the uid and bidDate of the max price on the condition that i have some auction id

see
i can have many bids with same auctionid but there will be one max bidPrice amongst dem, i want that price the biddate(date of that record entered) and that uid(user id who made dat bid)|||This would have been alot easier with DDL, DML (which you gave) and sample data..

If you post like this, it'll be easier for people to help

here you go...

CREATE TABLE xt_bid (Uid int IDENTITY(1,1), bidPrice money, bidDate datetime, auctionId int)
GO

INSERT INTO xt_bid (bidPrice, bidDate, auctionId)
SELECT 22.00, '1900-01-01 12:30:00', 1 UNION ALL
SELECT 23.00, '1900-01-01 12:31:00', 1 UNION ALL
SELECT 25.00, '1900-01-01 12:32:00', 1 UNION ALL
SELECT 24.00, '1900-01-01 12:33:00', 1
GO

SELECT *
FROM xt_bid o
WHERE bidPrice IN (SELECT MAX(BidPrice)
FROM xt_bid i
WHERE auctionId = 1)

GO

DROP TABLE xt_bid
GO|||?!?|||Just cut and paste it in to QA, and execute it...

is the what?!? is for?

Is there a question there?|||Brett, just curius of ur DML script
--
INSERT INTO xt_bid (bidPrice, bidDate, auctionId)
SELECT 22.00, '1900-01-01 12:30:00', 1 UNION ALL
SELECT 23.00, '1900-01-01 12:31:00', 1 UNION ALL
SELECT 25.00, '1900-01-01 12:32:00', 1 UNION ALL
SELECT 24.00, '1900-01-01 12:33:00', 1
--

Whats the difference in this script with ur script?

INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)
INSERT INTO xt_bid (bidPrice, bidDate, auctionId) values (blah bla blah)

I have not used that INSERT statement yet...with UNION ALL clause?

Thanks in advance

-bernie|||Did you try it?

It's just 1 insert as compared to 4...

which has less overhead?

It's so small it doesn't matter, but it's alot easier to cut and paste examples...

Isn't more tedious the other way?

Thursday, February 9, 2012

A few compilations/sec

Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
times. But the recompilations/sec could average around 0 or 1..
How can i find out what sprocs are triggering the compiles ?And why would
they ?
I tried to look at sysprocesses at the waitresource column for values that
have a TAB%[COMPILE] in them and found some sprocs but they look very simple
and straighforward. So whats causing a compile ? Could it be the code ?A compile is when you execute a statement / procedure and there is no plan
for it in the procedure cache. This usually occurs when you have adhoc sql
or poorly formatted batches. Take a look at sysproccache table and order by
USECOUNTS DESC. You will see all the ones at the top that are reused and
the ones near the bottom are not.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
> times. But the recompilations/sec could average around 0 or 1..
> How can i find out what sprocs are triggering the compiles ?And why would
> they ?
> I tried to look at sysprocesses at the waitresource column for values that
> have a TAB%[COMPILE] in them and found some sprocs but they look very
> simple and straighforward. So whats causing a compile ? Could it be the
> code ?
>|||There are so many sprocs with usecounts of 1.. There must be a better way to
narrow it down , is there not ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>A compile is when you execute a statement / procedure and there is no plan
>for it in the procedure cache. This usually occurs when you have adhoc sql
>or poorly formatted batches. Take a look at sysproccache table and order
>by USECOUNTS DESC. You will see all the ones at the top that are reused
>and the ones near the bottom are not.
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why would
>> they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>|||Well then you have a lot that don't get reused. You can use trace along with
the SP events that track cache hits and such.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>>A compile is when you execute a statement / procedure and there is no plan
>>for it in the procedure cache. This usually occurs when you have adhoc sql
>>or poorly formatted batches. Take a look at sysproccache table and order
>>by USECOUNTS DESC. You will see all the ones at the top that are reused
>>and the ones near the bottom are not.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why
>> would they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>>
>|||Use the plan_handle to get the T-SQL batches that are use donly once. For
SQL 2005 the query looks like:
select *, (select [text] from sys.dm_exec_sql_text(p.plan_handle))
from sys.dm_exec_cached_plans p
where usecounts = 1
order by size_in_bytes desc
Also, go ahead and read the 'Execution Plan Caching and Reuse' topic in BOL
http://msdn2.microsoft.com/en-us/library/ms181055(en-US,SQL.90).aspx .
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eh273SfKGHA.208@.tk2msftngp13.phx.gbl...
> There are so many sprocs with usecounts of 1.. There must be a better way
> to narrow it down , is there not ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eI5UedeKGHA.2628@.TK2MSFTNGP15.phx.gbl...
>>A compile is when you execute a statement / procedure and there is no plan
>>for it in the procedure cache. This usually occurs when you have adhoc sql
>>or poorly formatted batches. Take a look at sysproccache table and order
>>by USECOUNTS DESC. You will see all the ones at the top that are reused
>>and the ones near the bottom are not.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:%23sqEZFdKGHA.2040@.TK2MSFTNGP14.phx.gbl...
>> Im seeing an average of 4-5 compilations/sec with max at 60s and 70s at
>> times. But the recompilations/sec could average around 0 or 1..
>> How can i find out what sprocs are triggering the compiles ?And why
>> would they ?
>> I tried to look at sysprocesses at the waitresource column for values
>> that have a TAB%[COMPILE] in them and found some sprocs but they look
>> very simple and straighforward. So whats causing a compile ? Could it be
>> the code ?
>>
>

Tuesday, March 20, 2012

a too long query MAX(CASE WHEN

Thursday, March 8, 2012

a simple group by query, plz help

Thursday, February 9, 2012

A few compilations/sec

A few compilations/sec

A few compilations/sec

SQL server questions

Blog Archive

About Me