SQL Server CE: Selecting combining MAX and Distinct?

Question

I am very new to SQL. I have a table that is basically a results table for a bunch of projects. Different users working on different projects with various dates and levels of completion.

Here is the layout of the table:

tblProjectLog:

    [plogID] int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [projID] int NOT NULL, 
    [userID] int NOT NULL, 
    [presID] int NOT NULL, 
    [plogDateTime] datetime NOT NULL, 
    [plogNote] nvarchar(100)

Here is my initial SQL:

select 
    plog.plogID, plog.userID, plog.projID, plog.presID,  
    u.userFirstName + ' ' + u.userLastName AS [User], 
    proj.projName, pres.presResult, plog.plogDateTime, plog.plogNote 
from 
    tblProjectLog plog 
inner join 
    tblUser u on plog.userID = u.userID 
inner join 
    tblProject proj on plog.projID = proj.projID 
inner join 
    tblProjectResult pres on plog.presID = pres.presID 
order by 
    plog.plogID asc

It returns everything (which it should because there are not MAX or DISTINCT statements). So what I get is this:

plogID    userID    projID    presID    User    projName    presResult    plogDateTime    plogNote
2    1    1    2    John Smith    Birdhouse    Completed    2015-06-13 10:54:45.873    Well done. Sanding needs work.
3    3    1    2    Katie Williams    Birdhouse    Completed    2015-06-13 13:55:54.813    Excellent staining with solid work.
4    2    7    1    John Thomas    Step Ladder    In Progress    2015-06-13 15:57:20.593    going along well
5    6    2    3    Diane Stevens    Chessboard    Withdrawn    2015-06-13 16:57:57.393    leaving town for 2 weeks. will start new when she returns.
6    7    4    1    Lia McKenzie    Lamp    In Progress    2015-06-13 17:36:39.183    Working on electical routing
7    8    5    1    Stephanie Drake    Toolbox 1    In Progress    2015-06-14 11:41:27.907    solid starting work
8    7    4    1    Lia McKenzie    Lamp    In Progress    2015-06-14 11:52:25.923    finished sanding. first day of stain.
9    9    3    1    Cheryl Lor    Step Stool    In Progress    2015-06-14 11:59:30.113    Learning to use a table saw. Nice start.
10    2    7    2    John Thomas    Step Ladder    Completed    2015-06-15 11:20:51.737    good finished project.
11    7    4    2    Lia McKenzie    Lamp    Completed    2015-06-15 11:21:31.723    Excellent work.
12    2    1    1    John Thomas    Birdhouse    In Progress    2015-06-15 13:08:53.407    starting complicated birdhouse design

Cleaning it up to make it easier to read (removing the ID fields (except for the plogID which is the primary key on tblProjectLog) and extraneous fields) give us this:

2 | John Smith | Birdhouse | Completed 
3 | Katie Williams | Birdhouse | Completed
4 | John Thomas | Step Ladder | In Progress
5 | Diane Stevens | Chessboard | Withdrawn
6 | Lia McKenzie | Lamp | In Progress
7 | Stephanie Drake | Toolbox | In Progress
8 | Lia McKenzie | Lamp | In Progress
9 | Cheryl Lor | Step Stool | In Progress
10 | John Thomas | Step Ladder | Completed
11 | Lia McKenzie | Lamp | Completed
12 | John Thomas | Birdhouse | In Progress

What I would like to get is:

2 | John Smith | Birdhouse | Completed 
3 | Katie Williams | Birdhouse | Completed
5 | Diane Stevens | Chessboard | Withdrawn
7 | Stephanie Drake | Toolbox | In Progress
9 | Cheryl Lor | Step Stool | In Progress
11 | Lia McKenzie | Lamp | Completed
12 | John Thomas | Birdhouse | In Progress

But when I try adding in a MAX(plog.plogID), I get this error:

ErrorCode: -2147467259 [SQL Server Compact ADO.NET Data Provider] HResult: -2147217900, NativeError: 25518 ErrorMessage: In aggregate and grouping expressions, the ORDER BY clause can contain only aggregate functions and grouping expressions.

When I add in a DISTINCT tag on the plog.userID field, I get a token error.

Could anyone please help me out in trying to get only the most recent entry by each userID and only one entry per userID? Is MAX and DISTINCT required or is there another way for a sql noobie to get this done?

Oh, and I forgot to mention that it is SQL Server CE. Thanks for any help that you might be able to lend.

Answer 1

A user on another website posted an answer for me that worked perfectly. I would like to give credit to the user (and the website) but I'm not sure about the rules concerning posting other websites and/or their users.

Here is the sql statement that he posted and it provides exactly what I was looking for. Hopefully this will help other people if they are looking for something similar to what I was looking for.

select plog.plogID, plog.userID, plog.projID, plog.presID, 
     u.userFirstName + ' ' + u.userLastName AS [User], 
    proj.projName, pres.presResult, plog.plogDateTime, plog.plogNote
from tblProjectLog plog 
inner join tblUser u on plog.userID = u.userID 
inner join tblProject proj on plog.projID = proj.projID 
inner join tblProjectResult pres on plog.presID = pres.presID 
inner join
    (SELECT MAX(plog.plogID) as m_plogID        
    FROM tblProjectLog plog 
    GROUP BY plog.userID
    ) M
    ON m_plogID = plog.plogID
order by plog.plogID asc

Hope this helps. :)