Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Generating Sequence Number....

    Hello folks,
    I have a table where the records are like followings.
    ID Value
    ---------------
    1 aa
    1 aa
    1 aa
    1 bb
    1 bb
    1 bb
    1 bb
    1 cc
    2 pp
    2 dd
    2 dd
    3 qq
    4 aa

    I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.
    ID Value SeqId
    -----------------------------
    1 aa 1
    1 aa 2
    1 aa 3
    1 bb 1
    1 bb 2
    1 bb 3
    1 bb 4
    1 cc 1
    2 pp 1
    2 dd 1
    2 dd 2
    3 qq 1
    4 aa 1


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I cannot remember if you need to declare a length for a varchar column when used in a convert expression. Well, you'll either have 1 character or the correct number, so an easy test.

    Your question about the actual expression appears a little too easy, which makes me feel I might have misread it. From what I understand it is that you need to accomplish, here is the basic idea behind a solution:

    Code:
    select
      (convert(nvarchar, ID) + ' ' + Value) as 'NewSeq'
    Of course you will need to modify the formatting to meet your exact requirement, but it does nonetheless appear to be little more than a straight forward concatenation of two fields.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    I wish I could have done that Robert.... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.






    Thanks,
    Rahul Jha

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Quote Originally Posted by DBA_Rahul
    I wish I could have done that Robert.... But he problem is that the SeqId columnis of INT datatype. Hence can't concatenete.

    Thanks,
    Rahul Jha
    If that is the case, then your question does not make sense from what you have explained. Either there is more to the the problem than has been mentioned or I've really misread it.

    Your question asked how to concatenate a numeric and a non-numeric column to produce a single column. In any database, such a concatenation has the possibility to produce a non-numeric result. In your example, each and every row, will produce this kind of result.

    Yet you now say that the destination column is of type INT, which makes your question impossible to answer.

    Please do excuse me if I haven't understood, but the problem appears to be incredibly simple. Unless of course you want to store the concatenation of the numerical representation of the characters in the value column with the numeric value in the ID column. Could this be what you are trying to achieve?

    My apologies, but I found it very difficult to ascertain your requirements other than what I assumed above, from your original question.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    You are right Robert. Mistake was from my side only. Actually i should'nt have written (ID + Value). Anyways, what I am looking for is a new column named SeqId (INT) that will contain the data as per the column ID and Value. That means for a particular combination of ID & Value the value will be 1 and then for the same ID but different set of Value the data will be 2 and then for the next set of value for the same ID it will be 3 and so on.......... And the same process will start for the next ID........



    Thanks,
    Rahul Jha

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    No, you did not make a mistake at all. I just did not read the question completely, specifically the sample output that you provided. I therefore apologise for my above comments, they are not really relevant.
    Last edited by r123456; 11-15-07 at 03:58.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You will need access to a temporary row identifier of some description so that you can distinguish between rows that have identical values in each of the columns that are to be used to determine the value for the SeqID column.

    A common approach to these types of problems in Oracle is to use the Rownum function in conjunction with a defined ordering of the set. If you are using SQL Server 2005, you can use the new ROW_NUMBER function to achieve this purpose. If you're using SQL Server 2000, the easiest approach would be to temporarily insert an identity column named rowID or similar, and of course ensure that the values within are unique.

    Once you have access to a rowID using one of the above methods, the SELECT query becomes a trivial self-join. The following code shows how to produce the result set that you described, using Oracle and the rownum function.

    Code:
    SQL> select * from myTable;
    
            ID VALUE
    ---------- ------
             1 aa
             1 aa
             1 aa
             1 bb
             1 bb
             1 bb
             1 bb
             1 cc
             2 pp
             2 dd
             2 dd
             3 qq
             4 aa
    
    13 rows selected.
    
    select
      t1.id,
      t1.Value,
      count(t2.rn) SeqID
    from
      (
      select 
        id, 
        value, 
        rownum as rn 
        from 
        (select id, value from myTable order by id, value)
      ) t1
    
      inner join 
      (
      select 
        id, 
        value, 
        rownum as rn 
      from (select id, value from myTable order by id, value)
      ) t2
      on t2.rn <= t1.rn
      and t1.id = t2.id
      and t1.value = t2.value
    
    group by
      t1.id,
      t1.value,
      t1.rn
    order by
      t1.id,
      t1.value,
      t1.rn;
    
    This query will produce the following result set.
    
            ID VALUE       SEQID
    ---------- ------ ----------
             1 aa              1
             1 aa              2
             1 aa              3
             1 bb              1
             1 bb              2
             1 bb              3
             1 bb              4
             1 cc              1
             2 dd              1
             2 dd              2
             2 pp              1
             3 qq              1
             4 aa              1
    
    13 rows selected.
    I do know that you can use a multiple table select query as the criteria for an update query, but I can't at this moment remember the exact syntax. However the Select query is what's important, and what I've supplied above will produce the set that you need.

    Regards,
    Last edited by r123456; 11-15-07 at 03:56.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Quote Originally Posted by r123456
    ID VALUE SEQID
    ---------- ------ ----------
    1 aa 1
    1 aa 2
    1 aa 3
    1 bb 1
    1 bb 2
    1 bb 3
    1 bb 4
    1 cc 1
    2 dd 1
    2 dd 2
    2 pp 1
    3 qq 1
    4 aa 1

    13 rows selected.

    This is not the resultset that i am looking for........ I am looking for something like this.........
    ID VALUE SEQID
    ---------- ------ ----------
    1 aa 1
    1 aa 1
    1 aa 1
    1 bb 2
    1 bb 2
    1 bb 2
    1 bb 2
    1 cc 3
    2 dd 1
    2 dd 1
    2 pp 2
    3 qq 1
    4 aa 1
    I am again sorry for my putting across a wrong question....... I got to know this only when you gave me the answer............ Sorry again guys.........



    Thanks,
    Rahul Jha

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    No problem. Nothing gets me more excited than a continuously changing business requirement.

    One moment whilst I excuse myself from talking to my wife , to rewrite the SQL. However, could you just confirm that you are now positive that what you supplied above is the result set that you want?
    Last edited by r123456; 11-15-07 at 06:49.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270


    I am 100% sure of my requirement this time.




    Thanks,
    Rahul Jha

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also Rahul - what version of SQL Server? This is pretty trivial in 2005 using the OVER clause and RANK()

  12. #12
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

    Whatever solution you're most comfortable with I guess.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  13. #13
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    My DB is SQL SERVER 2K (Unfortunetly)



    Thanks,
    Rahul Jha

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r123456
    You can use a correlated sub-query if you'd rather, which is basically what the over () would provide in this instance.

    Whatever solution you're most comfortable with I guess.
    Although OVER is more efficient. Not in 2k of course.... unless you count not running at all as efficient.

  15. #15
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Actually it was that very approach that allowed me to save my previous firm millions of pounds in manual labour costs.

    "Rob, we need to improve the efficiency of this process. Could you please take a look"

    "Ok, I've concluded the best approach to take is to just delete the process. In other words if we do not run it, the execution time will be zero."

    "Oh good heavens, why didn't I think of that. This is what we need in this department, people who can think outside of the box."

    "Exactly! Now that will be 250 pounds please."
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •