MS ACCESS distinct on one column

I'm having a hard time trying to writhe a query against a MS ACCESS database. The table (tab) I'm selecting from contains 3 fields: id, rId and time. I want to select a record for every rId there is, and I want the one with the highest time. The query I tried was like this:

SELECT * FROM tab GROUP BY rId ORDER BY time;

However, that gives me all kinds of er开发者_开发技巧rors, like error in order by, and agrigator function not given enough parameters or something like that. Is this query simply impossible to write, or am I just going at it the wrong way.

[Note]

This is not something I'm trying to do in the designer, and I'm not making a report. I need a query that can return the data needed.

If you have a GROUP BY clause you're required to have a aggregate function. Believe the error message.

Try this:

SELECT rId, MAX(time) as highest FROM tab GROUP BY rId ORDER BY highest desc;

You're going to have issues with columns with names like time. It's not very descriptive, and it could be a keyword clash.

EDITED, based on feedback/comment original post.

based on the feedback, I would write my query as

select rID,
       max( ID ) as LastIDAdded,
       max( Time ) as LastDateTimeHistory
   from
       YourTable
   group by 
      rid

Now, in Access, by using their GUI, you have to let the query know you are doing totals. Select your table and add the 3 columns. Then, in the row that indicates what you are going to "total", you would put MAX() under the ID and Time columns. Additionally, when doing totals, there will be a GROUP BY row, you want the do select the checkbox under your rID column. Thats the best of my mental memory of the Access query interface, and unsure based on the version you are running. HTH.

ORIGINAL ANSWER

Although @duffymo has an option of a typical query to get a max() grouped by, I'm not sure its exactly what you want. As you stated, you have 3 fields. It appears your "id" is a surrogate, or auto-increment ID key for retaining unique rows in a table. Your "rID" is what you want to group by, and the time is to get the most recent time within a given group.

Based on how the records are added, and happened for a prior client, the records were handled in a scheduling system where any record could have any "time" stamped to it. So sequentially, you COULD get something like

ID   rID   Time
1     1     1pm
2     2     4pm
3     2     5pm
4     1     9AM
5     2     3pm

In such case, if you grouped by rID, your MAX() on time for rID = 1 would be the 1pm slot with ID = 1.  And for group rID = 2, the MAX() time of 5pm would be ID = 3.

That being said, if you are intending to utilize the "ID" column to tie-back to, you'll want to get the correct "ID" record too which in BOTH these scenarios is NOT the last "ID" sequence per the group by "rID".

That with all the other less than wonderful ability to design queries in Access through the designer and not code directly, it might be a bit trickier and require you to write a function that actually performs the final query you want.

If this scenario is what you are looking for, I'll try to dig back and craft the query that would work on above sample. and re-post back.

Try this:

SELECT * FROM tab t1 WHERE NOT EXISTS
   (SELECT * FROM tab t2 WHERE t2.rid = t1.rid AND t2.time > t1.time);

If I understand your question correctly, you do not want to use GROUP BY. You don't want aggregate data from groups of rows, you want actual complete rows from the original table but you want to filter out all but the most recent record.

For Access, you can use the SQL Select query I present here:

For example you have this table:

CLIENTE|| NOMBRES || MAIL

888 || T800 ARNOLD || t800.arnold@cyberdyne.com

123 || JOHN CONNOR || s.connor@skynet.com

125 || SARAH CONNOR ||s.connor@skynet.com

And you need to select only distinct mails. You can do it with this:

SQL SELECT:

SELECT MAX(p.CLIENTE) AS ID_CLIENTE
, (SELECT TOP 1 x.NOMBRES 
FROM Rep_Pre_Ene_MUESTRA AS x 
WHERE x.MAIL=p.MAIL 
 AND x.CLIENTE=(SELECT MAX(l.CLIENTE) FROM Rep_Pre_Ene_MUESTRA AS l WHERE x.MAIL=l.MAIL)) AS NOMBRE, 
p.MAIL
FROM Rep_Pre_Ene_MUESTRA AS p
GROUP BY p.MAIL;

You can use this to select the maximum ID, the correspondent name to that maximum ID , you can add any other attribute that way. Then at the end you put the distinct column to filter and you only group it with that last distinct column.

This will bring you the maximum ID with the correspondent data, you can use min or any other functions and you replicate that function to the sub-queries.

This select will return:

CLIENTE|| NOMBRES || MAIL

888 || T800 ARNOLD || t800.arnold@cyberdyne.com

125 || SARAH CONNOR ||s.connor@skynet.com

Remember to index the columns you select and the distinct column must have not numeric data all in upper case or in lower case, or else it won't work. This will work with only one registered mail as well. Happy coding!!!