Goup by clause confused....

[Summa]

Hi NG,

I have the following problem that I hope you can help me with (MS-SQL server
2000)

Imagine a statement like this:

"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"

I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc
not being in the clause...if I just inserts the "Group by lastname" in the
statement above.

How do I group these data?

--
regards,
Summa

[John Bell]

Hi

Your current statement does not make much sense! Without DDL (Create table
statements) and example data (as insert statements) and expected output, it
is hard to know what your really want.

But you may want to try

SELECT n.id, n.Firstname, max(t.id) as TestId, n.lastname
from nametable n JOIN TestData t on n.id = t.id
GROUP BY n.id, n.Firstname, n.lastname

John

"Summa" <su***@summarium.dk> wrote in message
news:cb***********@news.cybercity.dk...

Hi NG,

I have the following problem that I hope you can help me with (MS-SQL server 2000)

Imagine a statement like this:

"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"

I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc not being in the clause...if I just inserts the "Group by lastname" in the
statement above.

How do I group these data?

--
regards,
Summa

[Hugo Kornelis]

On Sun, 20 Jun 2004 14:28:51 +0200, Summa wrote:

Hi NG,

I have the following problem that I hope you can help me with (MS-SQL server
2000)

Imagine a statement like this:

"select id, firstname, (select top 1 id from testdata) as testid, lastname
from nametable order by firstname"

I would like to have this grouped by "lastname"...I assume that I have to
use the "Group by" clause, but it keeps complaining about id, firstname, etc
not being in the clause...if I just inserts the "Group by lastname" in the
statement above.

How do I group these data?

Hi Summa,

If you use group by, all columns in the select list must either appear in
the group by clause as well, or they must be an aggregation function. This
is the only way to make sure that SQL Server can unambiguously return the
correct results.

If you want to group by lastname, how should SQL Server present it's
results if two rows in nametable have the same lastname? Because of the
group by, only one row may be returned with this lastname - but which id
and firstname should be displayed?

I need to know more about your table structure, data and desired result to
give more specific aid. If you need more help, post the following:
* DDL for the relevant tables (CREATE TABLE statements, including all
relevant constraints),
* Sample data (in the form of INSERT statements),
* And expected output.
Best, Hugo
--

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

[Summa]

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:_R*******************@news-text.cableinet.net...

Your current statement does not make much sense! Without DDL (Create table
statements) and example data (as insert statements) and expected output, it is hard to know what your really want.

Ok? It was just example an statement...but suppose I have this table:

Table n:
id int
firstname ntext
lastname ntext

There are 5 records in that table (listed as id,firstname,lastname):

1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

My select MUST include a clause on the lastnames that gives me the
opportunity to specify them as a list - like this:

"Select id, firstname, lastname from n where lastname in
('Eastwood','Scwarzenegger') order by firstname"

That gives me the result
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

But I want this:
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

That is:
1: Grouped by lastname
2: The lastname specified first in the list-clause is also the
lastname-group that is listed first in the result.
My problems int the above:
1. How to group the data.
2. How to order the groupings (eg: Eastwood group comes.before
Scwarzenegger)
3. My data contains fields that cant be Grouped (ntext)

Hope u know what I mean now :)
--
regards,
Summa

[Summa]

Hi,

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

If you use group by, all columns in the select list must either appear in
the group by clause as well, or they must be an aggregation function. This
is the only way to make sure that SQL Server can unambiguously return the
correct results.
Ok...so if my tables contains ntext fields, I cannot group the data?
If you want to group by lastname, how should SQL Server present it's
results if two rows in nametable have the same lastname?

By the "order" clause? In no order if not specified...

Could I get you to see my reply to John Bell? - I have tried to soecify my
problems...:)

--
regards,
Summa

[John Bell]

Hi

Select id, firstname, lastname
from n
where lastname in ('Eastwood','Scwarzenegger')
order by lastname, firstname

Will give

3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

This is not grouped but ordered.

John

"Summa" <su***@summarium.dk> wrote in message
news:cb**********@news.cybercity.dk...

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:_R*******************@news-text.cableinet.net...

Your current statement does not make much sense! Without DDL (Create table statements) and example data (as insert statements) and expected output,

it

is hard to know what your really want.

Ok? It was just example an statement...but suppose I have this table:

Table n:
id int
firstname ntext
lastname ntext

There are 5 records in that table (listed as id,firstname,lastname):

1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

My select MUST include a clause on the lastnames that gives me the
opportunity to specify them as a list - like this:

"Select id, firstname, lastname from n where lastname in
('Eastwood','Scwarzenegger') order by firstname"

That gives me the result
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

But I want this:
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

That is:
1: Grouped by lastname
2: The lastname specified first in the list-clause is also the
lastname-group that is listed first in the result.
My problems int the above:
1. How to group the data.
2. How to order the groupings (eg: Eastwood group comes.before
Scwarzenegger)
3. My data contains fields that cant be Grouped (ntext)

Hope u know what I mean now :)
--
regards,
Summa

[Summa]

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:20*******************@news-text.cableinet.net...

Select id, firstname, lastname
from n
where lastname in ('Eastwood','Scwarzenegger')
order by lastname, firstname

Will give

3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

This is not grouped but ordered.
True, but correct me if Im wrong...this statement will not ensure that the
"Eastwood" listings comes before "Schwarzenegger". It just gives the correct
result because "E" comes before "S" in the alphabet.

Suppose that it wasnt lastnames - lets say we have en extra column in the
previous table. Lets call it "Categoryid". And that id maps to a table
called "Category";

Table category:
id int
Categoryname nvarchar(100)

-and it has these 3 records:

1 Test
2 MoreTest
3 EvenMoreTest

And the "n" table now looks like this:

Table n:
id int
categoryid int
firstname ntext
lastname ntext

Again, there are 5 records in that table (listed as
id,categoryid,firstname,lastname):

1 1 Tom Jensen
2 2 Arnold Scwarzenegger
3 3 Clint Eastwood
4 2 Helen Eastwood
5 3 Tim Scwarzenegger
Now, my sql looks like this:

"select n.id, n.firstname, n.lastname, category.categoryname from n inner
join category on n.categoryid = category.id where category.id in (2,3) order
by firstname"

How would I go about this? What I want is this result:

2 Arnold Scwarzenegger MoreTest
4 Helen Eastwood MoreTest
3 Clint Eastwood EvenMoreTest
5 Tim Scwarzenegger EvenMoreTest

This is:
Ordered with "MoreTest" before "EvenMoreTest" - like in the statement "...in
(2,3)..."

Notice that there might be 10 or 20 numbers in the list - clause. So I cant
rely on the lexical ordering whatsoever :(

--
Regards,
Summa




John

"Summa" <su***@summarium.dk> wrote in message
news:cb**********@news.cybercity.dk...

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:_R*******************@news-text.cableinet.net...

Your current statement does not make much sense! Without DDL (Create table statements) and example data (as insert statements) and expected

output, it

is hard to know what your really want.

Ok? It was just example an statement...but suppose I have this table:

Table n:
id int
firstname ntext
lastname ntext

There are 5 records in that table (listed as id,firstname,lastname):

1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

My select MUST include a clause on the lastnames that gives me the
opportunity to specify them as a list - like this:

"Select id, firstname, lastname from n where lastname in
('Eastwood','Scwarzenegger') order by firstname"

That gives me the result
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

But I want this:
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

That is:
1: Grouped by lastname
2: The lastname specified first in the list-clause is also the
lastname-group that is listed first in the result.
My problems int the above:
1. How to group the data.
2. How to order the groupings (eg: Eastwood group comes.before
Scwarzenegger)
3. My data contains fields that cant be Grouped (ntext)

Hope u know what I mean now :)
--
regards,
Summa

[John Bell]

Hi

The Order by clause is documented in books online or at
http://msdn.microsoft.com/library/de...rder_by_clause

To order by the category name alphabetically descending use:

select n.id, n.firstname, n.lastname, c.categoryname
from n join category c on n.categoryid = c.id
where c.id in (2,3)
order by c.categoryname desc, n.firstname asc

If you read Books online, you will see that you can order by columns not
specified in the select columns. Therefore if you want to order by ascending
categeory id then

select n.id, n.firstname, n.lastname, c.categoryname
from n join category c on n.categoryid = c.id
where c.id in (2,3)
order by n.categoryid, n.firstname

John
"Summa" <su***@summarium.dk> wrote in message
news:cb**********@news.cybercity.dk...

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:20*******************@news-text.cableinet.net...

Select id, firstname, lastname
from n
where lastname in ('Eastwood','Scwarzenegger')
order by lastname, firstname

Will give

3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

This is not grouped but ordered.
True, but correct me if Im wrong...this statement will not ensure that the
"Eastwood" listings comes before "Schwarzenegger". It just gives the

correct result because "E" comes before "S" in the alphabet.

Suppose that it wasnt lastnames - lets say we have en extra column in the
previous table. Lets call it "Categoryid". And that id maps to a table
called "Category";

Table category:
id int
Categoryname nvarchar(100)

-and it has these 3 records:

1 Test
2 MoreTest
3 EvenMoreTest

And the "n" table now looks like this:

Table n:
id int
categoryid int
firstname ntext
lastname ntext

Again, there are 5 records in that table (listed as
id,categoryid,firstname,lastname):

1 1 Tom Jensen
2 2 Arnold Scwarzenegger
3 3 Clint Eastwood
4 2 Helen Eastwood
5 3 Tim Scwarzenegger
Now, my sql looks like this:

"select n.id, n.firstname, n.lastname, category.categoryname from n inner
join category on n.categoryid = category.id where category.id in (2,3) order by firstname"

How would I go about this? What I want is this result:

2 Arnold Scwarzenegger MoreTest
4 Helen Eastwood MoreTest
3 Clint Eastwood EvenMoreTest
5 Tim Scwarzenegger EvenMoreTest

This is:
Ordered with "MoreTest" before "EvenMoreTest" - like in the statement "...in (2,3)..."

Notice that there might be 10 or 20 numbers in the list - clause. So I cant rely on the lexical ordering whatsoever :(

--
Regards,
Summa

John

"Summa" <su***@summarium.dk> wrote in message
news:cb**********@news.cybercity.dk...

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:_R*******************@news-text.cableinet.net...
> Your current statement does not make much sense! Without DDL (Create

table

> statements) and example data (as insert statements) and expected output, it
> is hard to know what your really want.

Ok? It was just example an statement...but suppose I have this table:

Table n:
id int
firstname ntext
lastname ntext

There are 5 records in that table (listed as id,firstname,lastname):

1 Tom Jensen
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

My select MUST include a clause on the lastnames that gives me the
opportunity to specify them as a list - like this:

"Select id, firstname, lastname from n where lastname in
('Eastwood','Scwarzenegger') order by firstname"

That gives me the result
2 Arnold Scwarzenegger
3 Clint Eastwood
4 Helen Eastwood
5 Tim Scwarzenegger

But I want this:
3 Clint Eastwood
4 Helen Eastwood
2 Arnold Scwarzenegger
5 Tim Scwarzenegger

That is:
1: Grouped by lastname
2: The lastname specified first in the list-clause is also the
lastname-group that is listed first in the result.
My problems int the above:
1. How to group the data.
2. How to order the groupings (eg: Eastwood group comes.before
Scwarzenegger)
3. My data contains fields that cant be Grouped (ntext)

Hope u know what I mean now :)
--
regards,
Summa

[Hugo Kornelis]

On Sun, 20 Jun 2004 16:06:35 +0200, Summa wrote:

Hi,

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

If you use group by, all columns in the select list must either appear in
the group by clause as well, or they must be an aggregation function. This
is the only way to make sure that SQL Server can unambiguously return the
correct results.

Ok...so if my tables contains ntext fields, I cannot group the data?

If you want to group by lastname, how should SQL Server present it's
results if two rows in nametable have the same lastname?

By the "order" clause? In no order if not specified...

Could I get you to see my reply to John Bell? - I have tried to soecify my
problems...:)

Hi Summa,

You are correct that you can't use ntext columns in group by. But are you
sure you need an ntext columns? They require lots of special handling; not
being able to use them in group by should be the least of your worries.
Are you absolutely sure you need more than 4000 characters??

From your exchange with John Bell, I see that you try to use group by to
achieve ordering. That is not correct. Group by is for grouping.

I'm sorry if I sound harsh, but I think you need to acquire at least a
basic understanding of SQL first. We can help you writing queries, but not
if you lack the basic skills and knowledge. A good starters' book can be
found here:
http://www.amazon.com/gp/reader/0201...057670-0048722
Best, Hugo
--

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

[Summa]

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:5B*******************@news-text.cableinet.net...

To order by the category name alphabetically descending use:

[Snip]

Somehow I get misunderstood, i'm afraid :(
Im aware of the Order by clause and its use - and if you read my post again
you'll se that this clause isnt what Im after...Notice at the end of my post
it says: "Notice that there might be 10 or 20 numbers in the list - clause.
So I cant rely on the lexical ordering whatsoever" - Or any "order by"
clause...

This is simple:
"select n.id, n.firstname, n.lastname, c.categoryname
from n join category c on n.categoryid = c.id
where c.id in (2,5,8,1,3)
order by c.categoryname desc, n.firstname asc"

The above select statement is going to produce a result that gives me the
listing in categoryid-order 2,5,8,1,3 ? No...of course not.

But thanks for trying anyway.

--
regards,
Summa

[John Bell]

Hi

There is no way to specify a random order like this without using something
like a temporary table or some other means to give it an order.

You can do something like:

select n.id, n.firstname, n.lastname, c.categoryname
from
( SELECT 1 AS id, 2 AS CategoryId
UNION ALL
SELECT 2, 5
UNION ALL
SELECT 3, 8
UNION ALL
SELECT 4, 1
UNION ALL
SELECT 5, 3 ) D join N ON n.categoryid = D.id
JOIN category c ON D.id = c.id
ORDER BY D.id, n.firstname

John

"Summa" <su***@summarium.dk> wrote in message
news:cb**********@news.cybercity.dk...

Hi,

"John Bell" <jb************@hotmail.com> wrote in message
news:5B*******************@news-text.cableinet.net...

To order by the category name alphabetically descending use: [Snip]

Somehow I get misunderstood, i'm afraid :(
Im aware of the Order by clause and its use - and if you read my post

again you'll se that this clause isnt what Im after...Notice at the end of my post it says: "Notice that there might be 10 or 20 numbers in the list - clause. So I cant rely on the lexical ordering whatsoever" - Or any "order by"
clause...

This is simple:
"select n.id, n.firstname, n.lastname, c.categoryname
from n join category c on n.categoryid = c.id
where c.id in (2,5,8,1,3)
order by c.categoryname desc, n.firstname asc"

The above select statement is going to produce a result that gives me the
listing in categoryid-order 2,5,8,1,3 ? No...of course not.

But thanks for trying anyway.

--
regards,
Summa

[Erland Sommarskog]

Summa (su***@summarium.dk) writes:

Ok? It was just example an statement...but suppose I have this table:

Table n:
id int
firstname ntext
lastname ntext

Permit me to bump in and point out that ntext is highly unsuitable for
name columns. Use nvarchar(50) or somesuch.

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

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