Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Need help with SQL

Sirish ReddyMay 16 2024

I am a J2EE developer and need help building a SQL query. I have data in my table that should be sorted based on certain conditions and struggling to build the query.

My Table

CREATE TABLE TEST_TR_TBL
(
  TR_ID           NUMBER(10)                    NOT NULL,
  ORIGINAL_TR_ID  NUMBER(10)                    NOT NULL,
  ITEM_ID         NUMBER(10)                    NOT NULL,
  NEW_ROW_FLG     VARCHAR2(1 BYTE)              DEFAULT 'N',
  POSITION_NUM    VARCHAR2(100 BYTE)            NOT NULL
)

My Data

SET DEFINE OFF;
Insert into TEST_TR_TBL Values (876983, 1144306, 138170, 'N', '6.78.1');
Insert into TEST_TR_TBL Values (876984, 1144307, 138170, 'N', '6.78.2');
Insert into TEST_TR_TBL Values (876985, 1144308, 138170, 'N', '6.78.3');
Insert into TEST_TR_TBL Values (876986, 1144309, 138170, 'N', '6.78.4');
Insert into TEST_TR_TBL Values (876987, 1144310, 138170, 'N', '6.78.5');
Insert into TEST_TR_TBL Values (876988, 1144311, 138170, 'N', '6.78.6');
Insert into TEST_TR_TBL Values (876989, 1144312, 138170, 'N', '6.78.7');
Insert into TEST_TR_TBL Values (876990, 1144313, 138170, 'N', '6.78.8');
Insert into TEST_TR_TBL Values (876991, 1144314, 138170, 'N', '6.78.9');
Insert into TEST_TR_TBL Values (876992, 1144315, 138170, 'N', '6.78.10');
Insert into TEST_TR_TBL Values (876993, 1144316, 138170, 'N', '6.78.11');
Insert into TEST_TR_TBL Values (876994, 1144317, 138170, 'N', '6.78.12');
Insert into TEST_TR_TBL Values (884499, 0, 138170, 'Y', '6.78.3.1');
Insert into TEST_TR_TBL Values (884500, 0, 138170, 'Y', '6.78.5.1');
Insert into TEST_TR_TBL Values (884501, 0, 138170, 'Y', '6.78.9.1');
COMMIT;

I have a requirement to fetch the data for each ITEM_ID sorted by TR_ID.

Now the catch, each ITEM_ID can have some records with NEW_ROW_FLG as Y and these items need to be sorted based on POSITION_NUMBER. I cannot directly sort the entire data on POSITION_NUMBER as we are going to get 6.78.10, 6.78.11, 6.78.12 before 6.78.2

After executing the query I would like to fetch the data in the below order… Any hints or pointers on building the query would be highly appreciated.

876983 1144306 138170 N 6.78.1
876984 1144307 138170 N 6.78.2
876985 1144308 138170 N 6.78.3
884499 0 138170 Y 6.78.3.1
876986 1144309 138170 N 6.78.4
876987 1144310 138170 N 6.78.5
884500 0 138170 Y 6.78.5.1
876988 1144311 138170 N 6.78.6
876989 1144312 138170 N 6.78.7
876990 1144313 138170 N 6.78.8
876991 1144314 138170 N 6.78.9
884501 0 138170 Y 6.78.9.1
876992 1144315 138170 N 6.78.10
876993 1144316 138170 N 6.78.11
876994 1144317 138170 N 6.78.12

Thanks.

This post has been answered by Frank Kulash on May 17 2024
Jump to Answer
Comments
Post Details
Added on May 16 2024
2 comments
141 views