| SELECT help [message #18691] |
Mon, 11 February 2002 04:37  |
Irene
Messages: 11
Registered: February 2002
|
Junior Member |
|
|
Hi!
I'd like to know how can I solve my problem using SELECT statement.
I have a Table with columns ID, Position, and Number
(where ID is an ID of an item, Position is a name of
a place and Number describes how many items with that ID i have on that Position). Now, an item with a certain ID can be placed on many Positions (not only
one).
Problem: If an item is placed on more than one position, i'd like to find a Position, where there's most of that item. And that, for all items.
Thanks
|
|
|
|
| Re: SELECT help [message #18693 is a reply to message #18691] |
Mon, 11 February 2002 05:43  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
hi,
here is query that does the same. i am using scott.emp table and (job,deptno) field
select * from ( select job,deptno,count(deptno) tot from emp e group by job,deptno having count(*)>1) t1
where 0=(select count(*) from
( select job,deptno,count(deptno) tot
from emp e group by job,deptno having count(*)>1) t2 where t2.tot>t1.tot and t2.job=t1.job)
make these changes
emp-->your_table
job-->id
deptno-->position
hope it helps!!!
cheers
pratap
|
|
|
|
| Re: SELECT help [message #18695 is a reply to message #18691] |
Mon, 11 February 2002 06:49 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
try now and let me know if it solved your problem
create table irene
(id number,
postion number,
num number);
insert into irene values(1, 1, 10);
insert into irene values(1, 2, 1);
insert into irene values(1, 5, 15);
insert into irene values(2, 3, 1);
insert into irene values(2, 2, 4);
insert into irene values(3, 5, 1);
commit;
select *
from irene i1
where 0=(select count(*) from irene i2 where i2.num>i1.num and i1.id=i2.id)
and id in (select id from irene group by id having count(*)>1)
|
|
|
|
|
|
| Re: SELECT help [message #18712 is a reply to message #18691] |
Mon, 11 February 2002 22:21 |
Irene
Messages: 11
Registered: February 2002
|
Junior Member |
|
|
Thank You, Pratap, it works just fine :)
I tried your solution too, Suresh, but it doesn't
work how it should..
For anyone who's interested, here's another
answer that works:
SELECT ID, Position FROM Table AS T
WHERE Number=(SELECT Max(Number) FROM Table WHERE
ID=T.ID)
seeya!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
