| Need Sql query [message #624751] |
Thu, 25 September 2014 04:50  |
 |
mvrkr44
Messages: 132
Registered: December 2012
|
Senior Member |
|
|
i have a table
TABLEA:
CREATE TABLEA (EMP_NO NUMBER(9),STATUS NUMBER(9));
INSERT INTO TABLEA VALUES(123,1);
INSERT INTO TABLEA VALUES(1234,1);
INSERT INTO TABLEA VALUES(1235,1);
INSERT INTO TABLEA VALUES(1236,1);
INSERT INTO TABLEA VALUES(1237,1);
INSERT INTO TABLEA VALUES(1238,1);
SELECT * FROM TABLEA WHERE STATUS=1 AND ROWNUM<=3 FOR UPDATE ORDERBY STATUS ASC
I HAVE TWO SESSIONS ,AND I WIL RUN THE SAME QUERY ..NEED TO GET THE DIFFERENT RESULT
IN FIRST SESSION I NEED TO GET THE EMP_NO :123,1234,1235
IN SECOND SESSION :1236,1237,1238
HOW CAN I GET THE SAME?
Edited by Lalit : Added code tags
[Updated on: Thu, 25 September 2014 04:54] by Moderator Report message to a moderator |
|
|
|
| Re: Need Sql query [message #624752 is a reply to message #624751] |
Thu, 25 September 2014 04:57  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
What's up with your keyboard uppercase?
What is your DB version?
ROWNUM doesn't guarantee the order, so do you need the two result sets as FIRST 3 ROWS ONLY and NEXT 3 ROWS ONLY per the default ascending order on EMP_NO?
|
|
|
|
|
|
|
|
|
|
| Re: Need Sql query [message #624756 is a reply to message #624755] |
Thu, 25 September 2014 05:01 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
You could try skip locked, but it really depends on what the full requirement is. That's not going to be the most elegant.
|
|
|
|
|
|
| Re: Need Sql query [message #624758 is a reply to message #624757] |
Thu, 25 September 2014 05:12 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Read up on rownum and modify the query accordingly.
I urge you to think carefully about the solution though as you're forcing a level of serialization into this which is often bad news for scaling.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
