Finding latest 10 records

[macroCharlie]

I am trying to find the latest ten records from a table on 3 fields. There can be a multiple number of combinations of the first two fields with each record having a date. Example:

SELECT txtField1, txtField2, dtField3
FROM table
ORDER BY 1, 2, 3 DESC

This gives me all the records but all I want is the latest 10 records fom all the combinations of txtField1 and txtField2. Let's say txtField1 has 6 distinct values and txtField2 has 3 distinct values. This gives me 18 different combinations of txtField1 and txtField2. Now if I add the date field all I want is the latest 10 records from each combination. This would give me a total of 180 records. Any coding suggestions?

 

[genomon]

Not sure what you mean by "last ten".
If it's the last ten chronologically (which is the only way I can see to order the records), then you can do SELECT TOP 10 fieldname FROM tablename ORDER BY datefield DESC.

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[macroCharlie]

That would only give me the latest 10 records for the query. I need the latest 10 records for each combination of the other two fields.

 

[bborissov]

Assuming that Field3 is the DateTime field and you use SQL Server 2000 (In 2005 it is much more easier)

DECLARE @Test TABLE (txtField1 (proper type), txtField2 (properType), dField3 datetime, Id int IDENTITY(1,1))

INSERT INTO @Test (txtField1, txtField2, dField3)
SELECT txtField1,
       txtField2,
       dField3
FROM YourTable
ORDER BY txtField1,
         txtField2,
         dField3 DESC


SELECT Temp1.*
FROM @Temp Temp1
INNER JOIN (SELECT txtField1,
                   txtField2,
                   MAX(Id) AS Id
            FROM @Temp
            GROUP BY txtField1,txtField2) Tbl1
ON Temp1.txtField1 = Tbl1.txtField1 AND
   Temp1.txtField2 = Tbl1.txtField2 AND
   (Tbl1.Id - Temp1.Id + 1) < 11

Not tested but you got the idea.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[macroCharlie]

That works if I change MAX(Id) to MIN(Id) since the date field was sorted descending would put the latest date with the minimum ID.

 

[macroCharlie]

On 2nd review that didn't work. It selected all the records. But MIN(Id) should have the lastest date.

 

[bborissov]

Could you post some example data?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP