| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-15-04, 17:36
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 12
|
|
|
sql: GROUP BY trouble
|
|
how can i do the following query
select max(sal), deptname, deptno from department group by dept;
?
I know that this query is not allowed. correct is
select max(sal) from department group by dept;
but what if I want to include deptnam and deptno in result?
|
|
11-15-04, 23:39
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
select max(sal), deptname, deptno
from department
group by deptname, deptno
|
|
11-16-04, 18:13
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 12
|
|
|
|
Quote:
|
Originally Posted by r937
select max(sal), deptname, deptno
from department
group by deptname, deptno
|
It doesn't work
SELECT DEPTNAME, DEPTNO, MAX(SAL) GROUP BY DEPTNAME, DEPTNO;
*
ERROR at line 1:
ORA-00937: not a single-group group function
|
|
11-16-04, 19:21
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
Code:
SQL> select * from a;
ID VALUE
---------- ----------
1 10
1 20
2 40
2 50
SQL> select id, max(value)
2 from a
3 group by id;
ID MAX(VALUE)
---------- ----------
1 20
2 50
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
|
11-16-04, 19:30
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
Quote:
|
Originally Posted by stasik_du
SELECT DEPTNAME, DEPTNO, MAX(SAL) GROUP BY DEPTNAME, DEPTNO
|
the reason this didn't work is because you forgot the FROM clause
|
|
11-17-04, 15:32
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 12
|
|
Quote:
|
Originally Posted by r937
the reason this didn't work is because you forgot the FROM clause
|
I have included it but did not paste in reply.
|
|
11-17-04, 15:37
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 12
|
|
Quote:
|
Originally Posted by r123456
Code:
SQL> select * from a;
ID VALUE
---------- ----------
1 10
1 20
2 40
2 50
SQL> select id, max(value)
2 from a
3 group by id;
ID MAX(VALUE)
---------- ----------
1 20
2 50
|
Yes. Exactly. Now imagine that you have the same table but with another attribute date so it looks like Table a(id,value,date). And now you need to do the same
select id, max(value)
from a
group by id;
but also include dates in output. How would you do that?
select id, max(value),date
from a
group by id;
doesn't work.
neither
select id, max(value),date
from a
group by id, date;
|
|
11-17-04, 16:43
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
nor should it work
when you use GROUP BY, you cannot "also include" another column
that's the whole point of grouping
now, what you seem to want is "the row which has the max(value) in its group"
you started out asking how to do grouping, and i think this is what threw us off the track
you can achieve what you want with grouping, but it requires a self join, and isn't very pretty
a different way to approach it, which is a bit more elegant, and which also makes sense once you're familiar with it, is the correlated subquery:
Code:
select t1.id
, t1.value
, t1.date
from yourtable as t1
where t1.value
= ( select max(value)
from yourtable
where id = t1.id )
|
|
11-18-04, 15:28
|
|
Registered User
|
|
Join Date: Oct 2004
Posts: 12
|
|
Quote:
|
Originally Posted by r937
nor should it work
when you use GROUP BY, you cannot "also include" another column
that's the whole point of grouping
now, what you seem to want is "the row which has the max(value) in its group"
you started out asking how to do grouping, and i think this is what threw us off the track
you can achieve what you want with grouping, but it requires a self join, and isn't very pretty
a different way to approach it, which is a bit more elegant, and which also makes sense once you're familiar with it, is the correlated subquery:
Code:
select t1.id
, t1.value
, t1.date
from yourtable as t1
where t1.value
= ( select max(value)
from yourtable
where id = t1.id )
|
thanks a lot!
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
sql: GROUP BY trouble
