Goup by clause confused....
Summa
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
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:cb3vtu$2v1s$1...@news.cybercity.dk...
Hugo Kornelis
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
"John Bell" <jbellne...@hotmail.com> wrote in message
news:_RfBc.837$2p3.7...@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
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:qu2bd099pbssskjdb...@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
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:cb45ek$3sl$1...@news.cybercity.dk...
Summa
"John Bell" <jbellne...@hotmail.com> wrote in message
news:20hBc.902$XS3.7...@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 Bell
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" <su...@summarium.dk> wrote in message
news:cb4a74$941$1...@news.cybercity.dk...
Hugo Kornelis
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
Summa
"John Bell" <jbellne...@hotmail.com> wrote in message
news:5BiBc.975$IZ4.8...@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:
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
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:cb4nel$nat$1...@news.cybercity.dk...
Erland Sommarskog
> 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, esq...@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp