Get the first instance of a row using MS Access

Question

EDITED:

I have this query wherein I want to SELECT the first instance of a record from the table petTable.

SELECT id, 
    pet_ID, 
    FIRST(petName), 
    First(Description) 
FROM petTable 
GROUP BY pet_ID;

The problem is I have huge number of records and this query is too slow. I discovered that GROUP BY slows down the query. Do you have any idea that could make this query faster? or better, a query wherein I don't need to use GROUP BY?

Does your table not have a Primary Key? If pet_ID is the primary key then there is no need of using First as there should not be more than 1 record for the same pet. — PaulFrancis, Jul 25 '14 at 09:10
@paulFrancis , I just updated my post . Suppose I have id as primary key and pet_id may occur more than once in the table — slek, Jul 25 '14 at 09:14

score 2 · Answer 1 · answered Jul 25 '14 at 11:32

"The problem is I have huge number of records and this query is too slow. I discovered that GROUP BY slows down the query. Do you have any idea that could make this query faster?"

And an index on pet_ID, then create and test this query:

SELECT pet_ID, Min(id) AS MinOfid
FROM petTable
GROUP BY pet_ID;

Once you have that query working, you can join it back to the original table --- then it will select only the original rows which match based on id and you can retrieve the other fields you want from those matching rows.

SELECT pt.id, pt.pet_ID, pt.petName, pt.Description
FROM
    petTable AS pt
    INNER JOIN
    ( 
        SELECT pet_ID, Min(id) AS MinOfid
        FROM petTable
        GROUP BY pet_ID
    ) AS sub
    ON pt.id = sub.MinOfid;

PaulFrancis · Answer 2 · 2014-07-25T10:37:52.447

Your Query could change as,

SELECT ID, pet_ID, petName, Description
FROM petTable
WHERE ID IN 
(SELECT Min(ID) As MinID FROM petTable GROUP BY pet_ID);

Or use the TOP clause,

SELECT petTable.petID, petTable.petName, petTable.[description]
FROM petTable
WHERE petTable.ID IN
   (SELECT TOP 1 ID                            
   FROM petTable AS tmpTbl                              
   WHERE tmpTbl.petID = petTable.petID        
   ORDER BY tmpTbl.petID DESC) 
ORDER BY petTable.petID, petTable.petName, petTable.[description];

Get the first instance of a row using MS Access

2 Answers2

Linked