13 Replies Latest reply: Feb 21, 2013 9:20 AM by Christine Schnittker RSS

    query

    969952
      I have written the below query..
      O/P :
      based on STATUS_ID we are ordering. Except 1st row remaining rows giving the same result.(i.e. same values are repeated) it's not not displaying 11 to 20 rows exactly.

      The above query repeats the same records when MyRow BETWEEN1 and 10 or when MyRow BETWEEN 11 and 20 , the problem seems to be with sort order.

      Can you please look into this and let me know if any modifications required in the query.

      Thanks.

      Edited by: 966949 on Feb 19, 2013 1:54 PM

      Edited by: 966949 on Feb 26, 2013 6:24 AM
        • 1. Re: query
          Frank Kulash
          Hi,

          Sorry, I can't reproduce the problem.
          Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          966949 wrote:
          I have written the below query..
          SELECT *    FROM(
          SELECT
          CR_ID,
          EXPIRED_DATE,
          credential_name,
          CREDENTIAL_NUMBER,
          CREDENTIAL_REGION,               
          position,                       
          Row_Number() OVER( ORDER BY CR_ID DEsc ) MyRow
          from
          CREDENTIAL_OWNER.CREDENTIALS_VIEW 
          )
          where
          MyRow BETWEEN 11 and 20 ;
          The above query repeats the same records when MyRow BETWEEN1 and 10 or when MyRow BETWEEN 11 and 20 , the problem seems to be with sort order.

          Can you please look into this and let me know if any modifications required in the query.
          It looks okay to me. Assuming cr_id is unique, and nobody is changing the table, you should get different results every time you change the WHERE clause.
          • 2. Re: query
            Purvesh K
            By ordering the data on Status_ID column, if the values are not Unique you might get disputed results. However, if it was actually Unique, then you would have got a distinct result set.

            For the rows, where Status_ID is same, they would be ordered randomly and with no guarantee that the same ordering might happen next time you execute the same query. Hence, it is very much essential to have a unique/group of column while ordering, so as to ensure a distinct result set.

            Can you provide the output of below?
            SELECT *    FROM(
                            SELECT
                                            CR_ID,
                                            EXPIRED_DATE,
                                            credential_name,
                                            CREDENTIAL_NUMBER,
                                            CREDENTIAL_REGION,               
                                            position, status_id,
                            Row_Number() OVER( ORDER BY STATUS_ID DEsc ) MyRow
                                            from
                            CREDENTIAL_OWNER.CREDENTIALS_VIEW 
            )
            where
                            MyRow BETWEEN 1 and 20
            • 3. Re: query
              969952
              If am giving 1 to 20 limit i's showing the perfect results... but if am executing the query for the limitis 1 .. 10 and 11 to 20 it's giving the o/p as mentioned in the above one.

              I am planning to execute teh query differently and want to display the result in as expected...

              Thanks
              • 4. Re: query
                969952
                Please look into the result which i mentioned in the above one and guide me for the required changes.

                Thanks.
                • 5. Re: query
                  Christine Schnittker
                  Hi,

                  your posted output does not include the MyRow column, which it should, according to the posted select.
                  Also, please include status_id in your select (like already posted/requested by Purvesh).

                  Without that additional information it's hard to say what's going wrong.
                  • 6. Re: query
                    969952
                    Please find the below output after adding Status_ID column
                    1 to 10 :
                    -------
                    18221
                    17848
                    18464
                    19859
                    17971
                    18388
                    20129
                    18447
                    18009
                    17910
                    
                    11 to 20
                    ------
                    18016
                    17848
                    18464
                    19859
                    17971
                    18388
                    20129
                    18447
                    18009
                    17910
                    except 1st record remaining records are as it is after adding status_id.

                    Thanks.
                    • 7. Re: query
                      Christine Schnittker
                      Maybe you could still show us the "perfect result" for 1-20, the full output, including MyRow and status_id

                      Alternatively, a create table and the appropriate inserts to reproduce the data which is giving you trouble would help too.
                      • 8. Re: query
                        969952
                        N/A

                        Edited by: 966949 on Feb 26, 2013 6:23 AM
                        • 9. Re: query
                          Christine Schnittker
                          Hi,
                          your status_id seems to be constant 102. How do you expect any "order" from that?
                          What you posted before was a list of cr_id, not status_id. Do you mean to sort by cr_id?

                          //Tine
                          • 10. Re: query
                            969952
                            No. Need to sort by STATUS_ID only!!
                            • 11. Re: query
                              Christine Schnittker
                              Then I can only stress the point that ordering by a column apparently containing constant data will not gain you anything. I wouldn't call that "ordering".
                              The problem is (technically) not with your query, the problem is in the data.
                              //Tine
                              • 12. Re: query
                                969952
                                for this ( to order it by STATUS_ID with expected results, can we change any modifications in the query? ( instead of row_number or any other good way to resolve it?

                                thanks.
                                • 13. Re: query
                                  Christine Schnittker
                                  No.
                                  And after seeing the data, the results are expected ;)

                                  What order do you expect from 102, 102, 102, 102, 102, 102... ?