Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    3

    Question Unanswered: Loop Query to update the output

    I need to update the result depending upon the count of distinct entries.

    Example

    ID Employee Region State
    1 ABC AMEA MI
    2 DEF AMEA MI
    3 XYZ APAC TX


    I want the result as below

    ID Employee Region State
    1 ABC AMEA MI-1
    2 DEF AMEA MI-2
    3 XYZ APAC TX

    since the count of Region is 2

    I tried using DECLARE @intFlag INT and stuff but wasnt able to get the solution. ANy help would be highly appreciated.

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    This is air code so it may need tweaking. But the general idea should work.

    Code:
    with cte as
    (
       Select id, employee, region, state,
         Row_Number() Over(partition by region order by id) as rowNum
       From myTable
    )
    Select cte.id, cte.employee, cte.region,cte.state,cte.rowNum
     From cte

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    No, The count of distinct entries is a value that exists at a different level of aggr

    Please post DDL; please learn basic data modeling and follow ISO-11179 naming Standards.

    >> I need to update the result depending upon the count of distinct entries. <<

    No. The count of distinct entries is a value that exists at a different level of aggregation, not at the row level. We do not materialize computed values in tables; that was punch cards, not RDBMS.

    CREATE TABLE Foobars
    (foo_id INTEGER NOT NULL PRIMARY KEY,
    employee_name VARCHAR(35) NOT NULL,
    region_code CHAR(4) NOT NULL,
    state_code CHAR(2) NOT NULL);

    INSERT INTO Foobars
    VALUES
    (1, 'ABC', 'AMEA', 'MI'),
    (2, 'DEF', 'AMEA', 'MI'),
    (3, 'XYZ', 'APAC', 'TX');

    >> I want the result as below
    1 ABC AMEA MI-1
    2 DEF AMEA MI-2
    3 XYZ APAC TX

    since the count of region_code is 2 <<

    No. You just changed the Postal Service state codes! You do not have that authority. Trust me This is also a violation of First Normal Form (1NF); the column has two totally difference vales shoved into it.

    CREATE VIEW Sequenced_Foobars
    As
    SELECT foo_id, employee_name, region_code, state_code
    ROW_NUMBER()
    OVER (PARTITION BY region_code, state_code
    ORDER BY employee_name)
    AS regional_seq
    FROM Foobars;

    This view will always be correct, it is valid RDBMS and not require constantly updating that base table we had to guess at. Get qa copy of MANGA GUIDE TO DATABASE; it is a good guide to basics.

Tags for this Thread

Posting Permissions

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