return only 1 row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

return only 1 row

Author

Topic

kevinjsexton
Starting Member

2 Posts

Posted - 2009-11-02 : 16:49:55

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
Of course my real problem has many students.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-02 : 16:53:39

Here's one way:


select t.[name]
       ,t.[date]
       ,t.score
from   myTable t
inner join (
       select [name], min([date]) as dt
       from MyTable
       group by [name]
       ) d
              on d.[name] = t.[name]
              and d.dt = t.[date]

Be One with the Optimizer
TG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-03 : 00:19:59

if ur using 2005 and later try this one too

SELECT name,date,score FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Date DESC) as RID ,* FROM MyTable)s
WHERE Rid = 1

Pradip
Starting Member

32 Posts

Posted - 2009-11-03 : 00:30:16

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.

with question I will to modify answer posted by bklr...
SELECT name,date,score FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Date DESC) as RID ,* FROM MyTable where name='mike')s
WHERE Rid = 1

pradipjain

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-11-03 : 03:56:13

select name,score,date from
(
select name ,score,date ,row_number() over (partition by name order by name,date desc) [rank]
from score)
a where a.rank=1

kevinjsexton
Starting Member

2 Posts

Posted - 2009-11-03 : 12:32:24

(I am on SQL 2000)
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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-03 : 12:43:45

My solution works for 2000 - have you tried it?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources