I have a table of customers who have multiple credit scores recorded differents days. I am using SQL 2000 queries

to get the customer latest date credit score only. When I use Max(scoredate) with the customer-id and credit-score

in the group by clause, I still get the previous scoredates instead of just the max scoredate. However if, I don't

put the credit-score in the Select clause I get the max scoredate. How can I get the credit-score with the max

scoredate?

For example;

select max(scoredate) as scoredate, customer-id, credit-score
from customer-tbl
group by customer-id, credit-score

returns the following result:

scoredate customer-id credit-score
---------- ----------- ------------
2003-07-05 100131 715
2001-03-02 100131 738
2002-01-02 100157 706

I want it to return the following result:

scoredate customer-id credit-score
---------- ----------- ------------
2003-07-05 100131 715
2002-01-02 100157 706



Table DDL:
CREATE TABLE customer-tbl(Col1 datetime, Col2 int, Col3 int)

no you're not, you're using DB2

DB2 SQL Query to get the latest date only

Hi

Whatever your reason may be, in SQL Server, the answer for your question is as below. This is a question about selecting top n rows from a grouped records.

if object_id('tempdb.#customer_tbl') is not null
drop table #customer_tbl

CREATE TABLE #customer_tbl(Col1 datetime, Col2 int, Col3 int)

INSERT INTO #customer_tbl Values('2003-07-05', 100131, 715)
INSERT INTO #customer_tbl Values('2001-03-02', 100131, 700)
INSERT INTO #customer_tbl Values('2004-09-02', 100131, 38)
INSERT INTO #customer_tbl Values('2005-08-02', 100131, 73)
INSERT INTO #customer_tbl Values('2005-08-03', 100131, 78)
INSERT INTO #customer_tbl Values('2002-01-02', 100157, 706)
INSERT INTO #customer_tbl Values('2003-01-02', 100157, 706)
INSERT INTO #customer_tbl Values('2004-01-02', 100157, 706)

SELECT * FROM #customer_tbl t
WHERE
(
Select Count(*)
FROM #customer_tbl
WHERE t.Col2=Col2
and Col1>t.Col1
)<1