Post reply

Select history of records

  • September 1, 2011 at 2:26 am

    #244098

    Hi All,

    Please find the below

    CREATE TABLE answers(question varchar(20), Answer Varchar(20),answereddate smalldatetime, accredappid int)

    INSERT INTO answers VALUES ('qa300','1200','2009-09-15 11:57:00','18604')

    INSERT INTO answers VALUES ('qa400','1200','2009-09-15 11:57:00','18604')

    INSERT INTO answers VALUES ('qa200','21/11/2006','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa300','1.32','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa400','1.25','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa400','1.32','2010-10-11 11:18:00','23829')

    INSERT INTO answers VALUES ('qa200','29/11/2004','2010-03-29 16:56:00','23400')

    INSERT INTO answers VALUES ('qa300','1.87','2010-03-29 16:57:00','23400')

    INSERT INTO answers VALUES ('qa400','1.87','2010-03-29 17:01:00','23400')

    INSERT INTO answers VALUES ('qa200','23/09/2006','2010-03-29 16:07:00','23370')

    INSERT INTO answers VALUES ('qa300','2.54','2010-03-29 16:07:00','23370')

    INSERT INTO answers VALUES ('qa300','2.45','2010-03-29 19:26:00','23370')

    INSERT INTO answers VALUES ('qa400','2.45','2010-03-29 19:34:00','23370')

    My aim is to select all the questions and answers where there are more than one answer for any of the questions (qa200 or qa300 or qa400) for a given accredappid i.e, my results should be all questions and answers of accredappid's 23829 and 23370. I have like 1 million records in answers table.

    I really could not find a way to do this.

    Any help is really appreciated.

    Thanks,

    D

  • September 1, 2011 at 3:05 am

    #1375844

    How's this?

    SELECT b.*

    FROM (SELECT question

    FROM answers

    GROUP BY question

    HAVING COUNT(question) > 1) a

    INNER JOIN answers b ON a.question = b.question

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 1, 2011 at 3:23 am

    #1375849

    Thanks for the reply cadavre, but the result I am expecting should be

    INSERT INTO answers VALUES ('qa200','21/11/2006','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa300','1.32','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa400','1.25','2010-03-31 02:06:00','23829')

    INSERT INTO answers VALUES ('qa400','1.32','2010-10-11 11:18:00','23829')

    INSERT INTO answers VALUES ('qa200','23/09/2006','2010-03-29 16:07:00','23370')

    INSERT INTO answers VALUES ('qa300','2.54','2010-03-29 16:07:00','23370')

    INSERT INTO answers VALUES ('qa300','2.45','2010-03-29 19:26:00','23370')

    INSERT INTO answers VALUES ('qa400','2.45','2010-03-29 19:34:00','23370')

    i.e more than one answer to a question.

  • September 1, 2011 at 3:24 am

    #1375850

    SELECT b.*

    FROM (SELECT accredappid,

    question,

    COUNT(Answer) AS Expr1

    FROM answers

    GROUP BY accredappid,

    question

    HAVING ( COUNT(Answer) > 1 )) a

    INNER JOIN answers b

    ON a.accredappid = b.accredappid

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • September 1, 2011 at 3:48 am

    #1375858

    that helped.

    Thanks very much

  • September 1, 2011 at 4:08 am

    #1375870

    Here's another way:

    SELECT

    question,

    Answer,

    answereddate,

    accredappid

    FROM (

    SELECT

    question,

    Answer,

    answereddate,

    accredappid,

    AnswerCount = COUNT(*) OVER (PARTITION BY accredappid, question)

    FROM answers

    ) d

    WHERE AnswerCount > 1

    ORDER BY accredappid, question

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 2, 2011 at 1:50 pm

    #1376669

    just out of curiousity Chris, I assume there's no real difference between writing the query like that, or like "with ___ AS (blah) SELECT FROM cte WHERE count = 1"

  • September 5, 2011 at 4:34 am

    #1376996

    kramaswamy (9/2/2011)

    just out of curiousity Chris, I assume there's no real difference between writing the query like that, or like "with ___ AS (blah) SELECT FROM cte WHERE count = 1"

    Good question, and I don't know the answer. Onion selects like this seem to me to allow quite a few levels of nesting before the optimizer chokes, however I've seen the optimizer get well confused with a chain of only three CTE's.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply