This discussion is archived
11 Replies Latest reply: Dec 12, 2012 3:12 AM by Frank Kulash RSS

SQL Help Required

979294 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Can it be done with the Help of a single SQL query
  • 8. Re: SQL Help Required
    Purvesh K Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points