Home » SQL & PL/SQL » SQL & PL/SQL » SELECT help
SELECT help [message #18691] Mon, 11 February 2002 04:37 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #18699 is a reply to message #18691] Mon, 11 February 2002 07:30 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
select id,position from table1 where number1 in (select max(number1) from table1 group by id);
Re: SELECT help [message #18712 is a reply to message #18691] Mon, 11 February 2002 22:21 Go to previous message
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!
Previous Topic: Problem in duplicate record
Next Topic: how to see the result after sucessfully create the stored procedure
Goto Forum:
  


Current Time: Tue Aug 11 19:53:01 CDT 2020