Basic Aggregate/HAVING question

I want to find the order that has the largest quanity in pubs.dbo.sales
If I try to use an aggregate in the WHERE clause I get an error
--
SELECT ord_num
FROM pubs.dbo.sales
WHERE qty=max(qty)
/*
An aggregate may not appear in the WHERE clause unless it is in a subquery
contained in a HAVING clause
or a select list, and the column being aggregated is an outer reference.
*/
But I am almost certain that I used to be able to do this in Sybase T-SQL
and it worked:
SELECT ord_num
FROM pubs.dbo.sales
HAVING qty=max(qty)
Now in MS T-SQL I get these errors:
/*
Column 'sales.ord_num' is invalid in the select list because it is not
contained in an aggregate function
and there is no GROUP BY clause.
Column 'sales.qty' is invalid in the HAVING clause because it is not
contained in an aggregate function
and there is no GROUP BY clause.
*/
It looks like I can get what I want by either one of two ways
1. A subquery:
SELECT ord_num, qty
FROM pubs.dbo.sales
WHERE qty=(SELECT max(qty) FROM sales)
/*
ord_num qty
-- --
QA7442.3 75
*/
2. A GROUP BY with TOP
SELECT TOP 1 ord_num, max(qty) AS 'qty'
FROM pubs.dbo.sales
GROUP BY ord_num
ORDER BY max(qty) DESC
/*
ord_num qty
-- --
QA7442.3 75
*/
I have 2 questions:
1. Are there other (better) ways to write this?
2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?> But I am almost certain that I used to be able to do this in Sybase T-SQL
> and it worked:
> SELECT ord_num
> FROM pubs.dbo.sales
> HAVING qty=max(qty)
Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause is
applied after the aggregation so it doesn't make sense to reference a base
table column in this way unless it exists in the aggregate result.
> 1. Are there other (better) ways to write this?
I doubt it's any "better" but just for variety here's an alternative:
SELECT ord_num, qty
FROM Sales
WHERE qty >= ALL
(SELECT qty
FROM Sales)
I prefer the WHERE qty= subquery method.
> 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so, when?
Yes. HAVING without GROUP BY implies an aggregation across the whole set in
the same way that an aggregate function does without GROUP BY. The result
will be, at most, one row. You can use HAVING to return a value only if some
condition is met:
SELECT MIN(ord_date)
FROM Sales
HAVING MIN(ord_date)<='20000101'
And it can be especially useful in conjunction with EXISTS:
...
EXISTS -- Is ord_date unique?
(SELECT 1
FROM Sales
HAVING COUNT(DISTINCT ord_date)=COUNT(*))
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks David for the detailed answers.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:H4idnZ3fHI4t8lOi4p2dnA@.giganews.com...
> > But I am almost certain that I used to be able to do this in Sybase
T-SQL
> > and it worked:
> >
> > SELECT ord_num
> > FROM pubs.dbo.sales
> > HAVING qty=max(qty)
> Sybase used to have a sloppy, proprietary interpretation of the GROUP BY
> syntax. This query is not valid ANSI SQL. Conceptually the HAVING clause
is
> applied after the aggregation so it doesn't make sense to reference a base
> table column in this way unless it exists in the aggregate result.
>
> > 1. Are there other (better) ways to write this?
> I doubt it's any "better" but just for variety here's an alternative:
> SELECT ord_num, qty
> FROM Sales
> WHERE qty >= ALL
> (SELECT qty
> FROM Sales)
> I prefer the WHERE qty= subquery method.
>
> > 2. In MS T-SQL, can you ever use HAVING without GROUP BY? And if so,
when?
> Yes. HAVING without GROUP BY implies an aggregation across the whole set
in
> the same way that an aggregate function does without GROUP BY. The result
> will be, at most, one row. You can use HAVING to return a value only if
some
> condition is met:
> SELECT MIN(ord_date)
> FROM Sales
> HAVING MIN(ord_date)<='20000101'
> And it can be especially useful in conjunction with EXISTS:
> ...
> EXISTS -- Is ord_date unique?
> (SELECT 1
> FROM Sales
> HAVING COUNT(DISTINCT ord_date)=COUNT(*))
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

Bar graphic chart is not in the same order with data table

Hi All,

I have a very simple bar-graphic report that has the following data:

Sale Region Total Sale

A 1,000,000

B 350,000

On the table, the data is sort by the Sale Region, so the A would come before the B. But on the bar-chart, the B is always displayed before the A. If it is a vertical bar chart, and then the B is on the left, the A is on the right. And if it's a hoz. bar chart, the B bar is on the top, and the A bar is on the bottom. I try to modify the RDL to make the bars displayed in order, but failed after many tries.

Here is the RDL code that I wrote in an attempt to make the bars display in ASC order, but does not work:

<CategoryGroupings>

<CategoryGrouping>

<DynamicCategories>

<Sorting>

<SortBy>

<SortExpression>Fields!SaleRegion.Value</SortExpression>

</SortBy>

</Sorting>

<Grouping Name="Chart1CategoryGroup">

<GroupExpressions>

<GroupExpression>=RowNumber(Nothing)</GroupExpression>

</GroupExpressions>

</Grouping>

<Label>=Fields!SaleRegion.Value</Label>

</DynamicCategories>

</CategoryGrouping>

</CategoryGroupings>

Can someone shed a light?

Thanks in advance

You are missing the "=" in the SortExpression. Right now it is just a constant string "Fields!SaleRegionValue".

Change it to:
<SortExpression>=Fields!SaleRegion.Value</SortExpression>

-- Robert

|||

That's it! Thanks Robert. You surely have a pair of hawk's eyes.

I have the chart sorted in order the way I wanted it too. However, I could not get the table order in the same way. I event go into the VS2005 report designer, set the sort property of the table. But no matter what order I set, the table is always displayed in a non-order. I added an ORDER BY clause in the SQL retrieve statement, the table is still displayed in a kind of random order.

Is this a known problem with Reporting Service? Or are there properties that I have to set in order for the table displayed on an ASC or DESC sequence?

Thanks in advance.

|||

It sounds like you have an explicit table detail group or a table group expression defined. In that case, you have to add the sorting directly on the grouping expression.

-- Robert

sql

Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backups over a network

I am using the following syntax in sql 2000 in order to transfer the backup across the network to a backup machine:

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'

The problem I am experiencing is that when I run this script, I get the response:

Access is denied.
0 file(s) copied

Can anyone tell me what I am doing wrong?Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

Still, don't you think it's a security thing?|||Originally posted by Brett Kaiser
Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

he is backing up locally and then trying to copy it over the network .. Brett

read the post again

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'|||Ok, ive given up and having sql do the move for me. Not exactly sure whats causing the problem, but I have worked around it. I have just written a bat file that copies over a mapped drive that is run thru the schedule tasks interface. Not as clean as I would like, but it works.

Thanks again guys

backup, truncation, behavior

Scenario:
I have a database in SQL Server 2000 with 90gb of data. It is growing every day with a some millions records. In order to get superior performance I rebuild the indexes every night. The job takes about 1.5 hours, and sometimes causes to the log file to grow to 30-40 gigs. I do a backup of the log files every 20 minutes, and this causes an log truncation to happen automatically (according to the MS documention).
Problem:
The log files makes the disk goes full. I cannot insert new disks at this point.
Question:
When rebuilding the indexes takes 1.5 hours, and I do a log backup (which also does a truncation) every 20 minutes, will the log backup ever get to do something with the transaction log while its still working on a transaction (doing the rebuild of index)?
Observation:
It seems like the 20 minute log backup is of no help because I see the log files grows so big.

BR
Henrik
NorwayIn the future, you may want to point your SQL specific questions tohttp://www.sqljunkies.com/forums/.

Here is some code I run in one of my jobs to shrink it down:

DBCC SHRINKDATABASE (dbname, 10)
DBCC SHRINKFILE(dbname_Log, 10)
BACKUP LOG Website WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbname_Log, 10)

This first shrinks the db and then the log file and sets the size to be 10 MB. Then, it backs up the log file and shrinks it again. When I'm done, I get about 8-10 MB off of the db and my log file will be only 1 MB.|||Thanks for you reply and interest.

DBCC SHRINKDATABASE AND SHRINKFILE actually removes data, which is not the intent.

The backup command issued is what I'm talking about. What happens if you issue a CREATE INDEX blabla WITH DROP at 09.00 PM, and this takes 1.5 hours. Then, at 09.20 you issue a BACKUP LOG blabla. Will the last command help on reducing the log file, or will it not do any work before 1.5 hours has past?

DBCC Indexdefrag is neither an option for me.

-Henrik