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]

Re: Goup by clause confused....

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" <summa@summariu m.dk> wrote in message
news:cb3vtu$2v1 s$1@news.cyberc ity.dk...[color=blue]
> Hi NG,
>
> I have the following problem that I hope you can help me with (MS-SQL[/color]
server[color=blue]
> 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,[/color]
etc[color=blue]
> 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
>
>[/color]

[Hugo Kornelis]

Re: Goup by clause confused....

On Sun, 20 Jun 2004 14:28:51 +0200, Summa wrote:
[color=blue]
>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?[/color]

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]

Re: Goup by clause confused....

Hi,

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:_RfBc.837$ 2p3.7002158@new s-text.cableinet. net...[color=blue]
> Your current statement does not make much sense! Without DDL (Create table
> statements) and example data (as insert statements) and expected output,[/color]
it[color=blue]
> is hard to know what your really want.[/color]

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,la stname):

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','Sc warzenegger') 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]

Re: Goup by clause confused....

Hi,

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:qu2bd099pb ssskjdb4jn7oho6 ivi67r68f@4ax.c om...
[color=blue]
> 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.[/color]

Ok...so if my tables contains ntext fields, I cannot group the data?
[color=blue]
> If you want to group by lastname, how should SQL Server present it's
> results if two rows in nametable have the same lastname?[/color]

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]

Re: Goup by clause confused....

Hi

Select id, firstname, lastname
from n
where lastname in ('Eastwood','Sc warzenegger')
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" <summa@summariu m.dk> wrote in message
news:cb45ek$3sl $1@news.cyberci ty.dk...[color=blue]
> Hi,
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:_RfBc.837$ 2p3.7002158@new s-text.cableinet. net...[color=green]
> > Your current statement does not make much sense! Without DDL (Create[/color][/color]
table[color=blue][color=green]
> > statements) and example data (as insert statements) and expected output,[/color]
> it[color=green]
> > is hard to know what your really want.[/color]
>
> 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,la stname):
>
> 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','Sc warzenegger') 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
>
>[/color]

[Summa]

Re: Goup by clause confused....

Hi,

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:20hBc.902$ XS3.7383096@new s-text.cableinet. net...
[color=blue]
> Select id, firstname, lastname
> from n
> where lastname in ('Eastwood','Sc warzenegger')
> order by lastname, firstname
>
> Will give
>
> 3 Clint Eastwood
> 4 Helen Eastwood
> 2 Arnold Scwarzenegger
> 5 Tim Scwarzenegger
>
> This is not grouped but ordered.[/color]

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,f irstname,lastna me):

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.catego ryname 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 "EvenMoreTe st" - 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







[color=blue]
>
> John
>
> "Summa" <summa@summariu m.dk> wrote in message
> news:cb45ek$3sl $1@news.cyberci ty.dk...[color=green]
> > Hi,
> >
> > "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> > news:_RfBc.837$ 2p3.7002158@new s-text.cableinet. net...[color=darkred]
> > > Your current statement does not make much sense! Without DDL (Create[/color][/color]
> table[color=green][color=darkred]
> > > statements) and example data (as insert statements) and expected[/color][/color][/color]
output,[color=blue][color=green]
> > it[color=darkred]
> > > is hard to know what your really want.[/color]
> >
> > 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,la stname):
> >
> > 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','Sc warzenegger') 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
> >
> >[/color]
>
>[/color]

[John Bell]

Re: Goup by clause confused....

Hi

The Order by clause is documented in books online or at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp?frame=true#_order_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" <summa@summariu m.dk> wrote in message
news:cb4a74$941 $1@news.cyberci ty.dk...[color=blue]
> Hi,
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:20hBc.902$ XS3.7383096@new s-text.cableinet. net...
>[color=green]
> > Select id, firstname, lastname
> > from n
> > where lastname in ('Eastwood','Sc warzenegger')
> > order by lastname, firstname
> >
> > Will give
> >
> > 3 Clint Eastwood
> > 4 Helen Eastwood
> > 2 Arnold Scwarzenegger
> > 5 Tim Scwarzenegger
> >
> > This is not grouped but ordered.[/color]
>
> True, but correct me if Im wrong...this statement will not ensure that the
> "Eastwood" listings comes before "Schwarzenegger ". It just gives the[/color]
correct[color=blue]
> 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,f irstname,lastna me):
>
> 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.catego ryname from n inner
> join category on n.categoryid = category.id where category.id in (2,3)[/color]
order[color=blue]
> 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 "EvenMoreTe st" - like in the statement[/color]
"...in[color=blue]
> (2,3)..."
>
> Notice that there might be 10 or 20 numbers in the list - clause. So I[/color]
cant[color=blue]
> rely on the lexical ordering whatsoever :(
>
> --
> Regards,
> Summa
>
>
>
>
>
>
>
>[color=green]
> >
> > John
> >
> > "Summa" <summa@summariu m.dk> wrote in message
> > news:cb45ek$3sl $1@news.cyberci ty.dk...[color=darkred]
> > > Hi,
> > >
> > > "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> > > news:_RfBc.837$ 2p3.7002158@new s-text.cableinet. net...
> > > > Your current statement does not make much sense! Without DDL (Create[/color]
> > table[color=darkred]
> > > > statements) and example data (as insert statements) and expected[/color][/color]
> output,[color=green][color=darkred]
> > > 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,la stname):
> > >
> > > 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','Sc warzenegger') 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
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Hugo Kornelis]

Re: Goup by clause confused....

On Sun, 20 Jun 2004 16:06:35 +0200, Summa wrote:
[color=blue]
>Hi,
>
>"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
>news:qu2bd099p bssskjdb4jn7oho 6ivi67r68f@4ax. com...
>[color=green]
>> 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.[/color]
>
>Ok...so if my tables contains ntext fields, I cannot group the data?
>[color=green]
>> If you want to group by lastname, how should SQL Server present it's
>> results if two rows in nametable have the same lastname?[/color]
>
>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...: )[/color]

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/0201433362/ref=sib_dp_pt/104-9057670-0048722

Best, Hugo
--

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

[Summa]

Re: Goup by clause confused....

Hi,

"John Bell" <jbellnewsposts @hotmail.com> wrote in message
news:5BiBc.975$ IZ4.8716484@new s-text.cableinet. net...
[color=blue]
> To order by the category name alphabetically descending use:[/color]
[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]

Re: Goup by clause confused....

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" <summa@summariu m.dk> wrote in message
news:cb4nel$nat $1@news.cyberci ty.dk...[color=blue]
> Hi,
>
> "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> news:5BiBc.975$ IZ4.8716484@new s-text.cableinet. net...
>[color=green]
> > To order by the category name alphabetically descending use:[/color]
> [Snip]
>
> Somehow I get misunderstood, i'm afraid :(
> Im aware of the Order by clause and its use - and if you read my post[/color]
again[color=blue]
> you'll se that this clause isnt what Im after...Notice at the end of my[/color]
post[color=blue]
> it says: "Notice that there might be 10 or 20 numbers in the list -[/color]
clause.[color=blue]
> 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
>
>[/color]

[Erland Sommarskog]

Re: Goup by clause confused....

Summa (summa@summariu m.dk) writes:[color=blue]
> Ok? It was just example an statement...but suppose I have this table:
>
> Table n:
> id int
> firstname ntext
> lastname ntext[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp