11 Replies Latest reply: Dec 12, 2012 5:12 AM by Frank Kulash RSS

    SQL Help Required

    979294
      I have a table (Employee) with columns as shown
      Id Name Text Time_stmp
      1 ASDD AA 09/10/2112
      1 ASDD AAT 09/10/2112
      2 BSDD TT 09/11/2012
      3 ASDD UU 09/12/2012

      I want to produce output as
      id name text_old text_new time
      1 ASDD AA AAT 09/10/2012
      2 BSDD AAT TT 09/11/2012
      3 ASDD TT UU 09/12/2012

      Please help....
        • 1. Re: SQL Help Required
          user in
          hi welcome to forum
          please check the below link and rewrite the question
          SQL and PL/SQL FAQ
          • 2. Re: SQL Help Required
            Frank Kulash
            Hi,

            Welcome to the forum!

            Whenever you have a question, it really helps if you post sample data in a form that people can use to re-create the problem and test their ideas. CREATE TABLE and INSERT statements are great; a WHERE clause like Purvesh used below, is good, too.
            Also, explain how you get the results you want from the given data. In this example, it looks like you want the text from one row copied onto the next row, but what defines "next"? Remember, there is no built in order to rows in a table. I assume that "next" in this problem means next in sort order of text.

            See the forum FAQ {message:id=9360002}; it has some very good tips for how to get good answers in this forum.

            Here's one way to do what you asked:
            WITH     got_analytics     AS
            (
                 SELECT     id
                 ,     name
                 ,     LAG (text)    OVER (ORDER BY text)
                                AS text_old
                 ,     text           AS text_new
                 ,     time_stmp     AS time
                 ,     ROW_NUMBER () OVER (ORDER BY text)
                                   AS r_num
                 FROM     employee
            )
            SELECT       id, name, text_old, text_new, time
            FROM       got_analytics
            WHERE       r_num     != 1
            ORDER BY  r_num
            ;
            976291 wrote:I have a table (Employee) with columns as shown
            Id Name Text Time_stmp
            1 ASDD AA 09/10/2112
            1 ASDD AAT 09/10/2112 ...
            I assume the year should be 2<b>0</b>12 on both rows above.

            Edited by: Frank Kulash on Dec 11, 2012 11:50 PM
            Added questions about "next".
            • 3. Re: SQL Help Required
              Purvesh K
              One way of doing it.
              with data as
              (
                select 1 id, 'asdd' name, 'aa' txt, to_date('09/10/2112', 'DD/MM/YYYY') dt from dual union all
                select 1, 'asdd', 'aat', to_date('09/10/2112', 'DD/MM/YYYY') from dual union all
                select 2, 'bsdd', 'tt', to_date('09/11/2012', 'DD/MM/YYYY') from dual union all
                select 3, 'asdd', 'uu', to_date('09/12/2012', 'DD/MM/YYYY') from dual
              )
              select id, name, txt, ntxt, dt
                from (
                      select id, name, txt,
                             lead(txt, 1, txt) over (partition by id order by txt) ntxt,
                             row_number() over (partition by id order by txt) rn,
                             dt
                        from data
                     ) a
               where a.rn = 1;
              
              ID                     NAME TXT NTXT DT                        
              ---------------------- ---- --- ---- ------------------------- 
              1                      asdd aa  aat  09-OCT-12                 
              2                      bsdd tt  tt   09-NOV-12                 
              3                      asdd uu  uu   09-DEC-12
              I just realized that I have mis-read the expected output posted.

              I do have a query with regards to it. Do you wish to apply the New Text only to ID 1 since it has multiple records and for others the Old Text should be same as New Text? If it is not, then Frank's solution is what you should be looking at.

              Edited by: Purvesh K on Dec 12, 2012 9:58 AM
              • 4. Re: SQL Help Required
                887479
                Why are you repeating the already given solution after half an hour..?

                Just by changing LAG with LEAD ...

                Strange..!
                • 5. Re: SQL Help Required
                  Purvesh K
                  884476 wrote:
                  Why are you repeating the already given solution after half an hour..?

                  Just by changing LAG with LEAD ...

                  Strange..!
                  Not if I had not checked Frank's solution. It was providing some different data. And I thought that the solution he posted is partially complete.
                  Sorry, I just realized that I have mis-read the OP's expected output.

                  Now, if you can check both the solutions, you would understand the reason of doing so.

                  Edited by: Purvesh K on Dec 12, 2012 9:57 AM
                  Striked.
                  • 6. Re: SQL Help Required
                    887479
                    Purvesh K wrote:
                    884476 wrote:
                    Why are you repeating the already given solution after half an hour..?

                    Just by changing LAG with LEAD ...

                    Strange..!
                    Not if I had not checked Frank's solution. It was providing some different data. And I thought that the solution he posted is partially complete.

                    Now, if you can check both the solutions, you would understand the reason of doing so.
                    Sorry for that... :(

                    I was not smart enough to see that difference - your observation was nice...
                    • 7. Re: SQL Help Required
                      979294
                      Can it be done with the Help of a single SQL query
                      • 8. Re: SQL Help Required
                        Purvesh K
                        976291 wrote:
                        Can it be done with the Help of a single SQL query
                        It is a single Query, isn't it?

                        Or it you are considering the Inner View, then in my opinion it is not possible. I am not sure if you can attain it using the Model clause.
                        But, I was wondering about the output you provided is exactly what you need. If yes, then you need to answer to Frank's question of ordering of records, else, please answer to my questions.
                        • 9. Re: SQL Help Required
                          979294
                          Actually The output is based on Text change... whenever a user AAAA is changing a text.. we need to capture the same...

                          so output is like AAAA changed the text from suppose xxx to YYYY

                          then it is captured in table .. now if he is continuously working on same... we need to capture the last worked record.... Can it be done using ROWNUM and Join concept..?
                          • 10. Re: SQL Help Required
                            Purvesh K
                            976291 wrote:
                            Actually The output is based on Text change... whenever a user AAAA is changing a text.. we need to capture the same...

                            so output is like AAAA changed the text from suppose xxx to YYYY

                            then it is captured in table .. now if he is continuously working on same... we need to capture the last worked record.... Can it be done using ROWNUM and Join concept..?
                            Alright. To put it succintly, you need to track the changes made to TEXT column for each user.

                            Maybe then,
                            instead of (expected output you provided)
                            id name text_old text_new time
                            1 ASDD AA AAT 09/10/2012
                            2 BSDD AAT TT 09/11/2012
                            3 ASDD TT UU 09/12/2012
                            you need this:
                            id name text_old text_new time
                            1 ASDD AA AAT 09/10/2012
                            2 BSDD TT TT 09/11/2012
                            3 ASDD UU UU 09/12/2012
                            And if it actually is, then you already have the solution in the query i provided. But you have to explain, How do we order the records if case if the Text is updated on same day? Is it on basis of Text Column?
                            Also, when you want to display the Latest updated Text column, what happens to the Date/Timestamp column? What value does it display? What should the logic be?

                            Am I still wrong in getting what you are expecting? If I am then you need to explain in more details.

                            To answer you another question of whether or not its possible to achieve using ROWNUM and Joins, I would say its possible but I prefer an Analytic function to rownum solely due to reliability of outcome using analytic function.
                            I would leave it to Experts to guide which amongst them is better.
                            • 11. Re: SQL Help Required
                              Frank Kulash
                              Hi,
                              976291 wrote:
                              Actually The output is based on Text change... whenever a user AAAA is changing a text.. we need to capture the same...

                              so output is like AAAA changed the text from suppose xxx to YYYY
                              Again, my question is how cn you tell that the test changed from 'xxx' to 'YYYY', and not the other way around?
                              For example, in your original message:
                              976291 wrote:
                              I have a table (Employee) with columns as shown
                              Id Name Text Time_stmp
                              1 ASDD AA 09/10/2112
                              1 ASDD AAT 09/10/2112
                              Did the text change from 'AA' to 'AAT', or did it change from 'AAT' to 'AA'? How can you tell, since all the other columns are identical?
                              then it is captured in table .. now if he is continuously working on same... we need to capture the last worked record....
                              What does "last" mean? Words like "last", "next" and "later" all refer to some way of sorting. How are your rows sorted?
                              Can it be done using ROWNUM and Join concept..?
                              Yes. Using ROWNUM like that usually requires 2 nested sub-queries. Earlier, you were objecting to any sub-queries, for some reason. What's wrong with sub-queries? If the fastest, simplest solution requires a sub-query, why wouldn't you want to use it?
                              The analytic ROW_NUMBER function is kind of like ROWNUM, but it is a lot more versatile. However, if you're going to use that analytic function, then you might as well use the analytic function LAG or LEAD.