Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Single Query

  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Unanswered: Single Query

    i hav a table of the form


    table EMP
    (
    EMPNAME varchar(50)
    )

    and the data in the table is

    record #1 abc
    record #2 abc
    record #3 abc
    record #4 abc
    record #5 abc
    record #6 abc

    can i use a single query so as to retain a single record in the table and remove the rest ?
    Cheers....

    baburajv

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    select distinct empname from emp?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Not if you don't have some kind of integer column to differentiate the records. You will need to select the DISTINCT values into a temp table, delete everything, then insert the DISTINCT values back into the table. After that, you need to put a unique constraint on the table so it doesn't happen again.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Single Query RETURNS

    thanks 4 the reply

    this was not a real world problem. this was an interview question that i faced.i told the interviewer that i can do this with three queries,
    (one to get the distinct records to a temp table ,second to delete all records from EMP Table and third to re-insert the value from temp Table)

    but my interviewer insisted on doing this using a single query only.

    first i thought of Using 'ROWID' ( i donno whether that is preset in MS SQL)
    if ROWID is ther , we can give the query like this
    (delete from EMP Where ROWID <> "the required record's rowid

    here also, ther is a similar problem..
    "how to determine the row id of the "required row"


    when our interview was over, i asked my interviewer for the answer.he didnt give an answer

    i just wanna know whether that is possible using a single query
    Cheers....

    baburajv

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    In sqlserver there's no rowid. Is the data in the table as 'record #1 abc' or did you mean there are six records in the table all with the same value 'abc'? If the first, did the interviewer specify which record to retain?

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Single Query Re Visited

    data is stored in the table as 'abc' only as

    abc
    abc
    abc
    abc
    abc
    abc
    ( the record# was indicated for information only)
    Cheers....

    baburajv

  7. #7
    Join Date
    Mar 2004
    Posts
    80
    <code>
    SET ROWCOUNT 5
    DELETE FROM EMP
    SET ROWCOUNT 0
    </code>
    may be this is what he is expecting.

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I would tell him he's smoking crack.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. Crack is the most likely answer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Yes. Crack is the most likely answer.
    I don't really think so. While I can't claim personal experience, one of my neighbors in college gave me a lot of vicarious experience. This sounds more like a halucinogenic to me, maybe peyote.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whatever it is, it is clearly delusional.

    I defer to your superior knowledge. Peyote it is...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the big deal?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE EMP(EMPNAME varchar(50))
    GO
    
    INSERT INTO EMP (EMPNAME) 
    SELECT 'abc' UNION ALL SELECT 'abc' UNION ALL SELECT 'abc' UNION ALL SELECT 'abc' UNION ALL SELECT 'abc' UNION ALL 
    SELECT 'def' UNION ALL SELECT 'def' UNION ALL SELECT 'def' UNION ALL SELECT 'def' UNION ALL SELECT 'def' 
    GO
    
    DECLARE @EMP TABLE (EMPNAME varchar(50))
    
    INSERT INTO @EMP(EMPNAME) SELECT EMPNAME FROM EMP
    
    TRUNCATE TABLE EMP
    
    INSERT INTO EMP(EMPNAME) SELECT DISTINCT EMPNAME FROM @EMP
    
    SELECT * FROM EMP
    GO
    
    DROP TABLE EMP
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The big deal is this interviewer told him it could be done with a single statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ya know...it actually helps when you read these things...

    My guess is that they're going for the SELECT DISTINCT Part...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jul 2004
    Posts
    60
    i'd answer something like

    Code:
    delete emp 
    from (select top 5 empname from emp) sub
    where emp.empname = sub.empname
    peace

Posting Permissions

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