Home » SQL & PL/SQL » SQL & PL/SQL » order and group by (oracle 9i linux)
order and group by [message #361749] Thu, 27 November 2008 21:12 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
CREATE TABLE MWP(  PK_ID     NUMBER(5),  NAME_REF  VARCHAR2(10 BYTE),  code    NUMBER(5),  USER_REF  VARCHAR2(5 BYTE),  REMARKS   VARCHAR2(5 BYTE));
CREATE TABLE MWPFK(  PK_ID    NUMBER(5),  FK_IDM1  NUMBER(5),  FK_IDM2  NUMBER(5));

Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1000, 'abc', 1017, 'm1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1001, 'def', 1017, 'm1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1002, 'ghi', 1017, 'm1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1003, 'jkl', 1017, 'm1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1004, 'mno', 1017, 'm1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1005, 'pqr', 1017, 'b1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1006, 'st', 1017, 'b1', 'test');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1007, 'uv', 1017, 'b1', 'data');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1008, 'wx', 1017, 'b1', 'data');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1009, 'yz', 1011, 'b1', 'data');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1010, 'ad1', 1017, 'b1', 'data');
Insert into MWP
   (PK_ID, NAME_REF, code, USER_REF, REMARKS)
 Values
   (1011, 'cdb1', 1017, 'b1', 'data');
COMMIT;

Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (1, 1000, 1002);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (2, 1000, 1003);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (3, 1000, 1007);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (4, 1001, 1005);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (5, 1001, 1008);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (6, 1001, 1011);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (7, 1004, 1006);
Insert into MWPFK
   (PK_ID, FK_IDM1, FK_IDM2)
 Values
   (8, 1004, 1010);
COMMIT;



hi
I need to group by FK_IDM1 and order by FK_IDM2
highest id first with respective FK_IDM1

is it possible to get the ouput in a single query output like this


FK_IDM2	FK_IDM1	PK_ID	NAME_REF code	USER_REF REMARKS
1011	1001	1011	cdb1	1017	b1	data
1008	1001	1008	wx	1017	b1	data
1005	1001	1005	pqr	1017	b1	test
1010	1004	1010	ad1	1017	b1	data
1006	1004	1006	st	1017	b1	test
1007	1000	1007	uv	1017	b1	data
1003	1000	1003	jkl	1017	m1	test
1002	1000	1002	ghi	1017	m1	test


thnaxs and regards
Re: order and group by [message #361751 is a reply to message #361749] Thu, 27 November 2008 22:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Is this what you wanted to do ?

SQL> SELECT FK_IDM2 , FK_IDM1,
  2         A.*
  3  FROM MWP A ,MWPFK B
  4  WHERE A.PK_ID= B.FK_IDM2
  5  ORDER BY 2 ASC, 3 DESC;

   FK_IDM2    FK_IDM1      PK_ID NAME_REF         CODE USER_ REMAR
---------- ---------- ---------- ---------- ---------- ----- -----
      1007       1000       1007 uv               1017 b1    data
      1003       1000       1003 jkl              1017 m1    test
      1002       1000       1002 ghi              1017 m1    test
      1011       1001       1011 cdb1             1017 b1    data
      1008       1001       1008 wx               1017 b1    data
      1005       1001       1005 pqr              1017 b1    test
      1010       1004       1010 ad1              1017 b1    data
      1006       1004       1006 st               1017 b1    test

8 rows selected.

SQL>


And why FK_IDM1 is not in order in your output ?

Smile
Rajuvan


Re: order and group by [message #361753 is a reply to message #361749] Thu, 27 November 2008 22:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I original thought the same solution Rajuvan provided, however, if you requirements were to have the results order by the max FK_IDM2 value and then descending in each grouping based on FK_IDM1, then the following may work.

SQL> select fk_idm2, fk_idm1, pk_id, name_ref, code, user_ref, remarks
  2  from (
  3     select max(fk_idm2) over (partition by fk_idm1) mx,
  4     fk_idm2, fk_idm1, b.pk_id, name_ref, code, user_ref, remarks
  5     from mwpfk a, mwp b
  6     where a.fk_idm2 = b.pk_id)
  7  order by mx desc, fk_idm2 desc;

   FK_IDM2    FK_IDM1      PK_ID NAME_REF         CODE USER_ REMAR
---------- ---------- ---------- ---------- ---------- ----- -----
      1011       1001       1011 cdb1             1017 b1    data
      1008       1001       1008 wx               1017 b1    data
      1005       1001       1005 pqr              1017 b1    test
      1010       1004       1010 ad1              1017 b1    data
      1006       1004       1006 st               1017 b1    test
      1007       1000       1007 uv               1017 b1    data
      1003       1000       1003 jkl              1017 m1    test
      1002       1000       1002 ghi              1017 m1    test

8 rows selected.


BTW...thanks for the create/insert statements !

[Updated on: Thu, 27 November 2008 22:32]

Report message to a moderator

Re: order and group by [message #361763 is a reply to message #361753] Thu, 27 November 2008 23:31 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
thanxs guys thanxs a lot it worked ... its was great

one clarification here without using partition by
cant we use group by and get the result
just a idea
Re: order and group by [message #361765 is a reply to message #361749] Fri, 28 November 2008 00:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


SQL> SELECT FK_IDM2, FK_IDM1, PK_ID, NAME_REF, CODE, USER_REF, REMARKS
  2  FROM (
  3   SELECT(SELECT MAX(FK_IDM2) FROM MWPFK C WHERE C.FK_IDM1 =A.FK_IDM1)  MX,
  4         FK_IDM2, FK_IDM1, B.PK_ID, NAME_REF, CODE, USER_REF, REMARKS
  5         FROM MWPFK A, MWP B
  6         WHERE A.FK_IDM2 = B.PK_ID)
  7  ORDER BY MX DESC, FK_IDM2 DESC;

   FK_IDM2    FK_IDM1      PK_ID NAME_REF         CODE USER_ REMAR
---------- ---------- ---------- ---------- ---------- ----- -----
      1011       1001       1011 cdb1             1017 b1    data
      1008       1001       1008 wx               1017 b1    data
      1005       1001       1005 pqr              1017 b1    test
      1010       1004       1010 ad1              1017 b1    data
      1006       1004       1006 st               1017 b1    test
      1007       1000       1007 uv               1017 b1    data
      1003       1000       1003 jkl              1017 m1    test
      1002       1000       1002 ghi              1017 m1    test

8 rows selected.

SQL>
Re: order and group by [message #361778 is a reply to message #361765] Fri, 28 November 2008 01:23 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
thanxs it worked gr8....
one more requirement i need to pass where caluse
eg:
WHERE A.FK_IDM2=1008
and get

FK_IDM2	FK_IDM1	PK_ID	NAME_REF code	REMARKS
1011	1001	1011	cdb1	1017	data
1008	1001	1008	wx	1017	data
1005	1001	1005	pqr	1017	test

[Updated on: Fri, 28 November 2008 01:25]

Report message to a moderator

Re: order and group by [message #361780 is a reply to message #361749] Fri, 28 November 2008 01:26 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Now it is the turn for you to try yourself.

Just try and post the code here .

Smile
Rajuvan.
Re: order and group by [message #361786 is a reply to message #361780] Fri, 28 November 2008 01:39 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
Got it i created one view and selected from that
Re: order and group by [message #361788 is a reply to message #361749] Fri, 28 November 2008 01:42 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Cool .. please paste here a I say .

Smile
Rajuvan
Re: order and group by [message #361789 is a reply to message #361788] Fri, 28 November 2008 01:48 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
no view directly from query

SELECT FK_IDM2, FK_IDM1, PK_ID, NAME_REF, AMENED, USER_REF, REMARKS
   FROM (
     SELECT(SELECT MAX(FK_IDM2) FROM MWPFK C WHERE C.FK_IDM1 =A.FK_IDM1)  MX,
           FK_IDM2, FK_IDM1, B.PK_ID, NAME_REF, AMENED, USER_REF, REMARKS
           FROM MWPFK A, MWP B
          WHERE A.FK_IDM2 = B.PK_ID)
		  where FK_IDM1 in (Select a.FK_IDM1 from MWPFK A where a.FK_IDM2=1008) 
   ORDER BY MX DESC, FK_IDM2 DESC;


Re: order and group by [message #361796 is a reply to message #361778] Fri, 28 November 2008 02:04 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@oracle_coorgi,
oracle_coorgi wrote on Fri, 28 November 2008 12:53
hi
thanxs it worked gr8....
one more requirement i need to pass where caluse
eg:
WHERE A.FK_IDM2=1008
and get

FK_IDM2	FK_IDM1	PK_ID	NAME_REF code	REMARKS
1011	1001	1011	cdb1	1017	data
1008	1001	1008	wx	1017	data
1005	1001	1005	pqr	1017	test



If you are restricting A.FK_IDM2 = 1008 then how do you expect to get the rest of the records for instance 1011 and 1005 as quoted by you. I didn't understand. Can you explain?

Regards,
Jo
Re: order and group by [message #364326 is a reply to message #361796] Mon, 08 December 2008 00:35 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
in my first post i wanted all the the records .
next i wanted only the record for 1008 (i need pass where caluse)...

thanxs and regards
...
Previous Topic: unique records
Next Topic: Need help (again) with " ORA-00984: column not allowed here"
Goto Forum:
  


Current Time: Fri Dec 09 21:44:45 CST 2016

Total time taken to generate the page: 0.11071 seconds