Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: SQL select statement and getting rid of repeats?

    I am doing a select on just one table. I am returning 2 columns, none of which are the primary key of the table. I actually have no idea what the primary key is.

    Anyways I want to make one of the columns the primary key for whatever is reading the select statement.

    So for example I have this as my select statement:

    SELECT DISTINCT DEPTID, DESCRSHORT FROM PS_DEPT_TBL;

    and something like this being kicked back:

    DEPTID DESCRSHORT
    ------- -------------------
    894000 Operations
    894000 Operations1
    895000 Multifamily
    896000 Applied Technology
    897000 Qual Assur
    897000 Qual Assur1

    For the two that have repeats, 894000 and 897000 I want to elliminate the repeat so just take the first one. Is there a way to do this with the select statement?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Easy, create an index, remove the duplicates and afterward you create the primary key:
    Code:
    create index idx_PS_DEPT_TBL
          on PS_DEPT_TBL (DEPTID);
    delete from PS_DEPT_TBL d
    where rowid > (
            select min(rowid) from PS_DEPT_TBL r
             where r.DEPTID = d.DEPTID);
    commit;
    drop index idx_PS_DEPT_TBL;
    alter table PS_DEPT_TBL 
    add constraint PK_PS_DEPT_TBL primary key (DEPTID);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by LKBrwn_DBA
    Easy, create an index, remove the duplicates and afterward you create the primary key:
    Code:
    create index idx_PS_DEPT_TBL
          on PS_DEPT_TBL (DEPTID);
    delete from PS_DEPT_TBL d
    where rowid > (
            select min(rowid) from PS_DEPT_TBL r
             where r.DEPTID = d.DEPTID);
    commit;
    drop index idx_PS_DEPT_TBL;
    alter table PS_DEPT_TBL 
    add constraint PK_PS_DEPT_TBL primary key (DEPTID);

    I dont have permissions to alter the table =(

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by JamesAvery22
    I dont have permissions to alter the table =(
    Then:

    Code:
    CREATE UNIQUE INDEX PK_PS_DEPT_TBL
           ON PS_DEPT_TBL (DEPTID);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by JamesAvery22

    For the two that have repeats, 894000 and 897000 I want to elliminate the repeat so just take the first one. Is there a way to do this with the select statement?
    use analytics
    PHP Code:
    select deptiddescrshort
    from 
     
    (SELECT DISTINCT 
       DEPTID

       
    DESCRSHORT,
       
    count(deptidover (partition by deptid order by deptidlisting 
      FROM PS_DEPT_TBL
    )
    where listing 1
    you could probably use first_value as well.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jul 2004
    Posts
    2
    This query may work for you as well.

    select deptid, min(descrshort)
    from ps_dept_tbl
    group by deptid

  7. #7
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by aggieman93
    This query may work for you as well.

    select deptid, min(descrshort)
    from ps_dept_tbl
    group by deptid

    This seems to work the best, the others return way too few rows for some reason?

    Also,

    Is there a way to modify the data as it comes back?

    Think this is my noobish talking but heres what Id like to happen

    If the above select statement returns this:

    881 MyDescription
    882 MyDescription

    Can I change the statement so it returns 3 rows for each? So instead of getting just those two rows Id get this

    881 MyDescription_Group1
    881 MyDescription_Group2
    881 MyDescription_Group3
    882 MyDescription_Group1
    882 MyDescription_Group2
    882 MyDescription_Group3
    Last edited by JamesAvery22; 07-22-04 at 11:55.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by JamesAvery22
    This seems to work the best, the others return way too few rows for some reason?
    Why do I get the feeling your requirements on your first post are not
    what you really want?

    The below will give you what you ask for in the first post.
    edited version:

    PHP Code:
    select deptiddescrshort 
    from 
    (select deptiddescrshort
       
    count(deptidover (partition by deptid order by deptidlisting 
      from ps_dept_tbl

    where listing 1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by The_Duck
    Why do I get the feeling your requirements on your first post are not
    what you really want?

    The below will give you what you ask for in the first post.
    edited version:

    PHP Code:
    select deptiddescrshort 
    from 
    (select deptiddescrshort
       
    count(deptidover (partition by deptid order by deptidlisting 
      from ps_dept_tbl

    where listing 1

    When I did a "select distinct deptid from ps_dept_tbl" I get 1500some rows. When I do that above code I got 300some...

    none of the 1500 rows that were returned were repeats, obviously.
    So if I only get 300 back something must be getting cut out.

    What I needed as 1500rows with those deptids but I just needed to add the descrshort. Which this code did:
    select deptid, min(descrshort)
    from ps_dept_tbl
    group by deptid

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    now I am wondering why my solution did not work
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by The_Duck
    now I am wondering why my solution did not work

    Sorry man =\


    I just found out I might not need any of the code. There were repeats because of the 1500 I get back there are ones I should NOT be reading. There are "inactive" rows in there and thats the reason why there were repeat IDs. I should only be getting 600 rows. Wish they'd label the columns better than "EFF" with data "I" or "A" for Status active or status inactive... =\


    So maybe EFF and DEPTID is the key...

Posting Permissions

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