Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding latest 10 records

Status
Not open for further replies.

macroCharlie

Programmer
Jul 31, 2006
17
US
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?
 
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.
 
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.
 
Assuming that Field3 is the DateTime field and you use SQL Server 2000 (In 2005 it is much more easier) :)
Code:
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
 
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.
 
On 2nd review that didn't work. It selected all the records. But MIN(Id) should have the lastest date.
 
Could you post some example data?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top