Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: return only 1 row

    (I'm using SQL Server)
    I'm drawing a blank on what seems to be a simple SQL problem.

    You have a name, a date and a score on a test. You took the test several times and I want to return ONLY 1 ROW which contains your score the FIRST time you took the test.

    My table: test_scores

    Name Score Date
    Mike 90 1/1/09
    Mike 80 11/28/09

    select min(date),score,Name from test_scores group by Score,Name
    this returns 2 rows. Of course my real problem has many students.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    You were almost there:
    Code:
    SELECT S.Name, S.Score, T.minDate
    FROM test_scores as S,
         (select Name, min(date) as minDate
          from test_scores group by Name
         ) as T
    WHERE S.Name = T.Name AND
          S.date = T.minDate
    But I would stop using reserved words ("date") for column names.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure you don't want the best score?

    Code:
    SELECT [Name], [Date], Score AS MAX_Score
      FROM test_scores o
     WHERE EXISTS (SELECT [Name], MAX(Score) AS MAX_Score
    				 FROM test_scores i
    				WHERE i.[Name] = o.[Name]
    			 GROUP BY [Name]
    			   HAVING MAX(Score) = o.Score)
    Last edited by Brett Kaiser; 11-03-09 at 11:07.
    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.

  4. #4
    Join Date
    Nov 2009
    Posts
    2
    Here is the real query.....I got it to work but I guess the requirement is that I need the
    First Blood Pressure/Response value taken for EACH patient.
    SELECT VisitID,Response AS SYS_BP_A
    FROM EdmPatientIntRepeatQueries o
    WHERE VisitID IN(select VisitID from zbartemp) AND QueryID = 'VS.BP' AND
    EXISTS (SELECT min(i.ActualDateTime),Response
    FROM EdmPatientIntRepeatQueries i
    WHERE i.VisitID IN(select VisitID from zbartemp) AND i.QueryID = 'VS.BP'
    GROUP BY Response
    HAVING min(ActualDateTime) = o.ActualDateTime)
    Current Results:
    VisitID Response
    V123 125
    V123 137
    V456 118
    V456 132

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by kevinjsexton View Post
    Here is the real query.....I got it to work
    Great

    but I guess the requirement is that I need the
    First Blood Pressure/Response value taken for EACH patient.
    Then I guess that last staement invalidates the first...doesn't it...

    I know you must not identify a patient by a visitID, so......

    How do you identify the patient? my Guess is that it's in here

    EdmPatientIntRepeatQueries

    Post the DDL for that Table
    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.

Posting Permissions

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