Refining SQL query

My query is returning too much data. Here is the query, some sample data and a sample of what I'd really like.

select sd.ident,sd.suniq, td.testc, td.testuniq, td.subtestc, ts.testscore, ud.metadept, max(ts.takendt)AS testdate
from studemo sd, stutests ts, testdef td, udefstu ud
where ts.suniq =sd.suniq
and td.testuniq 开发者_如何学编程= ts.testuniq
and ts.suniq = ud.suniq
and td.testuniq IN ('2000089', '2000090', '2000091', '2000092')
group by sd.suniq, sd.ident, td.testc, td.subtestc, ts.testscore, ud.metadept, ts.takendt, td.testuniq
order by suniq

Sample Output

ident   suniq   testc   testuniq    subtestc    testscore   metadept    testdate
102201  2001444 ADEPT   2000091 L3  23  NULL    09/01/2006 00:00
102201  2001444 ADEPT   2000092 L4  7   NULL    06/01/2007 00:00
101184  2001532 ADEPT   2000092 L4  5   NULL    09/01/2006 00:00
101184  2001532 ADEPT   2000092 L4  7   NULL    06/01/2006 00:00
101184  2001532 ADEPT   2000092 L4  7   NULL    06/01/2007 00:00
590122  2001950 ADEPT   2000091 L3  22  NULL    06/01/2007 00:00
590122  2001950 ADEPT   2000090 L2  32  NULL    09/01/2006 00:00
141058  2004980 ADEPT   2000089 L1  27  NULL    05/01/2006 00:00
141058  2004980 ADEPT   2000090 L2  28  NULL    01/25/2008 00:00
141058  2004980 ADEPT   2000090 L2  27  NULL    06/01/2007 00:00

Wanted Output

102201  2001444 ADEPT   2000092 L4  7   NULL    06/01/2007 00:00
101184  2001532 ADEPT   2000092 L4  7   NULL    06/01/2007 00:00
590122  2001950 ADEPT   2000091 L3  22  NULL    06/01/2007 00:00
141058  2004980 ADEPT   2000090 L2  28  NULL    01/25/2008 00:00

Try this. You have too many GROUP BY criteria, and need aggregates on the remaining fields. From you desired output they all look like MAX values to me:

select  sd.ident,
        sd.suniq, 
        td.testc, 
        MAX(td.testuniq) as TestUniq, 
        MAX(td.subtestc) as Subtestc, 
        MAX(ts.testscore) as TestScore, 
        MAX(ud.metadept) as metadept, 
        max(ts.takendt)AS testdate
from studemo sd, stutests ts, testdef td, udefstu ud
where ts.suniq =sd.suniq
and td.testuniq = ts.testuniq
and ts.suniq = ud.suniq
and td.testuniq IN ('2000089', '2000090', '2000091', '2000092')
group by sd.suniq, sd.ident, td.testc,
order by suniq

I think i know what you are after, you are trying to get the most recent (or highest valued) item to display exclusively... have you tried the 'distinct' keyword? If I had a test dataset to try a few queries on I would be able to get you the right answer pretty quick, but pulling it from the top of my head is another story

select distinct sd.ident,sd.suniq, td.testc, td.testuniq, td.subtestc, ts.testscore, ud.metadept, max(ts.takendt)AS testdate from studemo sd, stutests ts, testdef td, udefstu ud where ts.suniq =sd.suniq and td.testuniq = ts.testuniq and ts.suniq = ud.suniq and td.testuniq IN ('2000089', '2000090', '2000091', '2000092') group by sd.suniq, sd.ident, td.testc, td.subtestc, ts.testscore, ud.metadept, ts.takendt, td.testuniq order by suniq desc