TSQL: I want to use a SELECT statement with COUNT(*) AS 'name' and ORDER BY 'name'

[war_wheelan]

I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs
and want to ORDER BY 'IOI's'. I have been combing through the BOL, but
I don't even know what topic/heading this would fall under.

USE INDII
SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY FIXID

I know that it is a simple question, but perhaps someone could assist
me.

Thanks,

[Thomas R. Hummel]

Try ORDER BY COUNT(*) instead of ORDER BY FIXID.

-Tom.

[Dan Gidman]

This statement as written may not give you what your looking for.
Basically its going to return the number of rows in your table. that's
it. What are you trying to count?

[war_wheelan]

Tom,

I modified my query as per my understanding of your suggestion e.g.

USE INDII
SELECT COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
ORDER BY COUNT(*)

The above example works, it returns a total of all IOIs. That would be
fine except that I am trying to see a total number of IOIs for each
'FIXID' sorted by largest number of IOI's then FIXID.

[Thomas R. Hummel]

Your original message said:

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY FIXID

In your last message you removed the GROUP BY clause. I'm not sure why
both you and Dan assumed that I wanted that removed. Leave your
statement as is, but replace the ORDER BY:

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY COUNT(*)

-Tom.

[war_wheelan]

Tom,

Your suggestion worked.

Thanks.

[Hugo Kornelis]

On 3 Mar 2005 07:15:19 -0800, wa*********@yahoo.com wrote:

I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs
and want to ORDER BY 'IOI's'. I have been combing through the BOL, but
I don't even know what topic/heading this would fall under.

USE INDII
SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY FIXID

I know that it is a simple question, but perhaps someone could assist
me.

Hi war_wheelan,

Though the alias you assign to a column in the SELECT clause can't be
used in most of the other parts of a query, the ORDER BY clause is an
exception: here, you can refer to an alias. So you could just use

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY IOIs

Note that ORDER BY COUNT(*) (as suggested by Thomas) works just as well,
but is less portable. ANSI standard SQL doesn't allow expressions in the
ORDER BY clause. T-SQL does allow this, and I use it often to get
orderings that are otherwise hard to achieve, but I always choose the
ANSI-standard version if that works as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Rawheiser]

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:eu********************************@4ax.com...

On 3 Mar 2005 07:15:19 -0800, wa*********@yahoo.com wrote:

I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIs
and want to ORDER BY 'IOI's'. I have been combing through the BOL, but
I don't even know what topic/heading this would fall under.

USE INDII
SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY FIXID

I know that it is a simple question, but perhaps someone could assist
me.

Hi war_wheelan,

Though the alias you assign to a column in the SELECT clause can't be
used in most of the other parts of a query, the ORDER BY clause is an
exception: here, you can refer to an alias. So you could just use

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY IOIs

Note that ORDER BY COUNT(*) (as suggested by Thomas) works just as well,
but is less portable. ANSI standard SQL doesn't allow expressions in the
ORDER BY clause. T-SQL does allow this, and I use it often to get
orderings that are otherwise hard to achieve, but I always choose the
ANSI-standard version if that works as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

I am not sure of its ANSI-ness or portability,
but I have specified the column index of a result set in some of my order by
's

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY 2

[jarl]

Hugo Kornelis wrote:

[...] ANSI standard SQL doesn't allow expressions in the
ORDER BY clause. T-SQL does allow this, and I use it often to get
orderings that are otherwise hard to achieve, but I always choose the
ANSI-standard version if that works as well.

Actually, ORDER BY expression has been in the ANSI/ISO SQL standard for
6 years, since SQL-99.
Regards,
Jarl

[Hugo Kornelis]

On Fri, 04 Mar 2005 02:47:36 GMT, David Rawheiser wrote:

Hi David,

(snip)

I am not sure of its ANSI-ness or portability,
Adhering to ANSI standards will always improva portablility.
but I have specified the column index of a result set in some of my order by
's

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY 2

ANSI permits you to use either the name or the ordinal position of a
column in the result set in the ORDER BY clause. Using a column name is
not permitted, unless that column appears in the SELECT list without
alias (since in this case, the name of the column in the result set will
be equal to that column's name). Using an expression is never permitted.

Note that technically, the ORDER BY is not part of the SELECT statement,
but a cursor operation. In theory, the SELECT statement returns a set
(which is unordered by definition); the ORDER BY reads that set (and
nothing else!) in temp storage, sorts it, then outputs the rows in the
sorted order. Any DBMS that strictly follows this theoretical order can
never sort on anything that's not in the result set. But real products
may (and will) change the order for optimization. SQL Server doesn't
only optimize, it also allows ordering on expressions and on columns
that are not included in the SELECT clause.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 4 Mar 2005 00:22:17 -0800, ja**@mimer.com wrote:

Hugo Kornelis wrote:

[...] ANSI standard SQL doesn't allow expressions in the
ORDER BY clause. T-SQL does allow this, and I use it often to get
orderings that are otherwise hard to achieve, but I always choose the
ANSI-standard version if that works as well.

Actually, ORDER BY expression has been in the ANSI/ISO SQL standard for
6 years, since SQL-99.

Hi Jarl,

As far as I know, SQL-99 is an ISO standard only, not (yet?) adopted by
the ANSI committee.

I'm sure that Joe Celko will correct me if I'm wrong.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Rawheiser]

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:nh********************************@4ax.com...

On Fri, 04 Mar 2005 02:47:36 GMT, David Rawheiser wrote:

Hi David,

(snip)

I am not sure of its ANSI-ness or portability,

Adhering to ANSI standards will always improva portablility.

but I have specified the column index of a result set in some of my order
by
's

SELECT FIXID, COUNT(*) AS IOIs
FROM [dbo].[IOI_2005_03_03]
GROUP BY FIXID
ORDER BY 2

ANSI permits you to use either the name or the ordinal position of a
column in the result set in the ORDER BY clause. Using a column name is
not permitted, unless that column appears in the SELECT list without
alias (since in this case, the name of the column in the result set will
be equal to that column's name). Using an expression is never permitted.

Note that technically, the ORDER BY is not part of the SELECT statement,
but a cursor operation. In theory, the SELECT statement returns a set
(which is unordered by definition); the ORDER BY reads that set (and
nothing else!) in temp storage, sorts it, then outputs the rows in the
sorted order. Any DBMS that strictly follows this theoretical order can
never sort on anything that's not in the result set. But real products
may (and will) change the order for optimization. SQL Server doesn't
only optimize, it also allows ordering on expressions and on columns
that are not included in the SELECT clause.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Thanks ...
So in summary
if I want to be portable on a query i want ordered by an expression,
use the column index in the order by clause

[jarl]

Hugo Kornelis wrote:

As far as I know, SQL-99 is an ISO standard only, not (yet?) adopted by the ANSI committee.

I'm sure that Joe Celko will correct me if I'm wrong.

Best, Hugo

Hugo,

This is quite confusing.

If you go to the ANSI web store, http://webstore.ansi.org/ansidocstore,
and search for SQL, you'll find that the SQL-99 documents are named
something like INCITS/ISO/IEC 9075-5-1999 etc, all with a note
"formerly ANSI/ISO/IEC 9075-5-...". As you can see the ANSI part of the
label has been replaced with INCITS. (Whatever that means.)

And in the list these documents are labelled with the ANSI logo. I
don't know what that means... But I do know that ANSI is a member of
the ISO SQL Standards Committee.
Regards,
Jarl

[Hugo Kornelis]

On Fri, 04 Mar 2005 12:45:24 GMT, David Rawheiser wrote:

Thanks ...
So in summary
if I want to be portable on a query i want ordered by an expression,
use the column index in the order by clause

Hi David,

Either the column index, or the alias assigned to it in the SELECT
statement (keeping in mind that a column name by itself in the SELECT
will default to it's own name if no alias is provided).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

On 4 Mar 2005 05:34:07 -0800, ja**@mimer.com wrote:

This is quite confusing.

(snip)

Hi Jarl,

Now, I'm confused too.

What I posted is based on what I've read over the years in this and
other groups. I have no inside knowledge about this subject.

Let's hope that Joe Celko sees this exchange and posts a reply to
enlighten us.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Gert-Jan Strik]

AFAIK, ANSI SQL-92 does not allow expressions in the ORDER BY clause,
only names or ordinal positions of the columns in the resultset.

ANSI SQL-99 does allow expressions in the ORDER BY clause. Also, it
deprecated the use of ordinal positions.

So the most ANSI compliant way of writing the query would be the one
posted by Hugo:

SELECT FIXID, COUNT(*) AS IOIs
FROM IOI_2005_03_03
GROUP BY FIXID
ORDER BY IOIs

HTH,
Gert-Jan

ja**@mimer.com wrote:

Hugo Kornelis wrote:

As far as I know, SQL-99 is an ISO standard only, not (yet?) adopted

by

the ANSI committee.

I'm sure that Joe Celko will correct me if I'm wrong.

Best, Hugo

Hugo,

This is quite confusing.

If you go to the ANSI web store, http://webstore.ansi.org/ansidocstore,
and search for SQL, you'll find that the SQL-99 documents are named
something like INCITS/ISO/IEC 9075-5-1999 etc, all with a note
"formerly ANSI/ISO/IEC 9075-5-...". As you can see the ANSI part of the
label has been replaced with INCITS. (Whatever that means.)

And in the list these documents are labelled with the ANSI logo. I
don't know what that means... But I do know that ANSI is a member of
the ISO SQL Standards Committee.

Regards,
Jarl

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

Let's hope that Joe Celko sees this exchange and posts a reply to
enlighten us.

Celko was on the SQL-92 committee, and does not seem to have high
thoughts about SQL-99, so I would not count on him.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Sat, 5 Mar 2005 17:16:21 +0000 (UTC), Erland Sommarskog wrote:

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

Let's hope that Joe Celko sees this exchange and posts a reply to
enlighten us.

Celko was on the SQL-92 committee, and does not seem to have high
thoughts about SQL-99

Probably because embracing SQL-99 would force him to rewrite all his
canned replies <bg>
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[--CELKO--]

>> As you can see the ANSI part of the label has been replaced with
INCITS. (Whatever that means.) <<

First it was NCITS, which is pronounced "Insights" -- I think that was
National Committee for Information Technology Standards, then it was
"International" when they added the "I". Standards are a weird world.
I am just back from vacation and want to go to bed, but i will look it
up tomorow. I was on the "ANSI X3H2 Datatabase Standards Committee"
which started as part of the CODASYL committee, etc.