This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / Oracle group byI have a table like this:
id: company name age
1 a 20
2 a 21
3 a 33
4 b 22
5 b 26
6 c 71
7 c 77
My goal is that i'd like to get a query like this:
1 a 33
4 b 22
7 c 71
SELECT
id, companyname,age
FROM tablename
group by compabyname
Why every time i got all the records.
Thanks for any help
-aaaaaaaa(aaaa);
2002-9-18
{321}
(#754347@0)
-
你这个Query有什么逻辑呀? 我怎么不明白? 我都不明白, Oracle怎么知道你要做什么呀?
-decentboy(黄金重装甲骑士);
2002-9-18
(#754359@0)
-
Thanks, I just like to get all the company and with the lagrest age
-aaaaaaaa(aaaa);
2002-9-18
(#754528@0)
-
Simply to say
Can i get the MIN() column and other column in same sql.
-aaaaaaaa(aaaa);
2002-9-18
(#754539@0)
-
正确, 使用Max, Min....都可以
-decentboy(黄金重装甲骑士);
2002-9-18
(#754553@0)
-
在你的sql中，使用max。忘记具体的语句如何写的了
-lilyba(Sunshine);
2002-9-18
(#754536@0)
-
Thanks
SELECT
id,
companyname,
max(age)
FROM tablename
group by compabyname
But there are an error message, it seems i must group by id and companyname together,
-aaaaaaaa(aaaa);
2002-9-18
(#754548@0)
-
当然group应该包括这两个field，它是假设id或companyname可能有重复值的。
-lilyba(Sunshine);
2002-9-18
(#754558@0)
-
Thanks,the issue is
SELECT
id,
companyname,
max(age)
from table
group by id,companyname
The query above will get all the records, not companynames with largest age
-aaaaaaaa(aaaa);
2002-9-18
(#754576@0)
-
非聚合函数, 如MAX,MIN,AVG..., 的列必须出现在Group By子句中
-decentboy(黄金重装甲骑士);
2002-9-18
(#754561@0)
-
Thanks,
Just like you said, we must group by "id", but the results with get all ids, whatever it has a max age, my goal is only get the id that has max age in the name group
-aaaaaaaa(aaaa);
2002-9-18
(#754586@0)
-
exampletable like this:
id name age
1 a 10
2 a 11
3 a 12
SELECT
ID,
NAME,
MAX(AGE)
FROM TABLENAME
GROUP BY ID,NAME
THE RESULTS LIKE THIS:
1 a 10
2 2 11
3 a 12
mY goal is to get results like this:
3 a 12
-aaaaaaaa(aaaa);
2002-9-18
{253}
(#754597@0)
-
SELECT Table1.id, Table1.company, Table1.age FROM Table1 WHERE (((Table1.age) In (select max(age) from table1 group by company)));
-lilyba(Sunshine);
2002-9-18
(#754599@0)
-
也不对。
-lilyba(Sunshine);
2002-9-18
(#754606@0)
-
逻辑不成立.... :-) 我想用单独一个SQL 完成有点困难....
-decentboy(黄金重装甲骑士);
2002-9-18
(#754610@0)
-
:( 假设了age 没有重复的。可是哪有那么美的事。
-lilyba(Sunshine);
2002-9-18
(#754622@0)
-
一个query好像实现不了。
-lilyba(Sunshine);
2002-9-18
(#754614@0)
-
Thanks for your guys help, i have been woked on this sql couple hours
-aaaaaaaa(aaaa);
2002-9-18
(#754625@0)
-
非要只建一个query 么？
-lilyba(Sunshine);
2002-9-18
(#754630@0)
-
no, but at first i think this can be done in one sql
-aaaaaaaa(aaaa);
2002-9-18
(#754637@0)
-
if there is another conidition such as age>30
what shall i do,
it seems having only filter the group column
-aaaaaaaa(aaaa);
2002-9-18
(#754653@0)
-
Is this what you need?1. select id, companyname, age from tablename
where ( companyname, age ) in
(select companyname, max(age) from tablename
group by companyname )
order by id;
2. age > 30
select id, companyname, age from tablename
where ( companyname, age ) in
(select companyname, max(age) from tablename
group by companyname )
and age > 30
order by id;
-cloud2001(卷云溶月);
2002-9-18
{354}
(#754696@0)
-
哈哈，还是自己改过来了。
-guestagain(guest again);
2002-9-18
(#754747@0)
-
Thanks, that's it, it works pretty well
-aaaaaaaa(aaaa);
2002-9-18
(#754779@0)
-
try this :select t1.id, t1.company, t1.age from table1 as t1,(select company, max(age) as Maxage from table1 group by company) as t2 where t1.company=t2.company and t1.age = t2.Maxage
-guestagain(guest again);
2002-9-18
{173}
(#754730@0)
-
Thanks, but there are some sql errors
-aaaaaaaa(aaaa);
2002-9-18
(#754782@0)
-
I see, you are using Oracle, so remove two "as" in the SQL statement.
-guestagain(guest again);
2002-9-18
(#754840@0)
-
SELECT T1.id, T1.company, T1.age FROM Table1 T1 ,Table1 T2 WHERE T2.age In (select max(age) from table1 group by company) and T1.id = T2.id
-easyway(翠花，回家吧！);
2002-9-18
(#754800@0)
-
Thanks, but is only one table
-aaaaaaaa(aaaa);
2002-9-18
(#754804@0)
-
you can use same table in select many times just give different alias.
-easyway(翠花，回家吧！);
2002-9-18
(#754809@0)
-
这可是个很简单的问题啊, 为什么不自己琢磨呢? 还是热点话题呢, 看来rolia上太多闲着的IT人了, 如果是在其它技术网站上, 肯定没有人理.
-arthurxu(ArthurXu OCP8,9i);
2002-9-18
(#755237@0)
-
agree
-handd(handd);
2002-9-19
(#755881@0)