[resolved] SQLCE Select statement with Distinct and Max (I think)?

SQL_Noob · June 15, 2015, 5:48pm

As you can tell from my username I am new to SQL. I hope I do this correctly. I'm including the table creates and inserts that I've seen other people use. I'm not sure if I should include the table create and data for all four tables involved or just the create/insert for the table I am querying against. To be on the safe (although longer) side I'm going to include the create/insert for each table.

I am attempting to do a select statement on a table (tblProjectLog) that contains foreign keys to 3 other tables and a primary key field, a datetime field and a varchar field. Here are the table create/insert statements.

One thing I wasn't sure about was whether to include the IDENTITY field. I chose to do so because the main table I'm looking at does have those fields in there. I hope that is OK.

Oh, this is using a SQL Server Compact Edition 4.0 database.

##create tblUser
CREATE TABLE [tblUser] (
[userID] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[userFirstName] nvarchar(30) NOT NULL,
[userLastName] nvarchar(50)
)
GO

insert test data for tblUser

INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (1,N'John',N'Smith');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (2,N'John',N'Thomas');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (3,N'Katie',N'Williams');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (4,N'William',N'Brandt');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (5,N'Charles',N'Bunker');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (6,N'Diane',N'Stevens');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (7,N'Lia',N'McKenzie');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (8,N'Stephanie',N'Drake');
GO
INSERT INTO [tblUser] ([userID],[userFirstName],[userLastName]) VALUES (9,N'Cheryl',N'Lor');
GO

create tblProject

CREATE TABLE [tblProject] (
    [projID] int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    [projName] nvarchar(50) NOT NULL
)
GO

insert test data for tblProject

INSERT INTO [tblProject] ([projID],[projName]) VALUES (1,N'Birdhouse');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (2,N'Chessboard');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (3,N'Step Stool');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (4,N'Lamp');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (5,N'Toolbox 1');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (6,N'Toolbox 2');
GO
INSERT INTO [tblProject] ([projID],[projName]) VALUES (7,N'Step Ladder');
GO

create tblProjectResult

CREATE 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)
)
GO

insert test data for tblProjectResult

INSERT INTO [tblProjectResult] ([presID],[presResult]) VALUES (1,N'In Progress');
GO
INSERT INTO [tblProjectResult] ([presID],[presResult]) VALUES (2,N'Completed');
GO
INSERT INTO [tblProjectResult] ([presID],[presResult]) VALUES (3,N'Withdrawn');
GO

create tblProjectLog

CREATE 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)
)
GO

insert test data into tblProjectLog

INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (2,1,1,2,'20150613 10:54:45.873',N'Well done. Sanding needs work.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (3,1,3,2,'20150613 13:55:54.813',N'Excellent staining with solid work.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (4,7,2,1,'20150613 15:57:20.593',N'going along well');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (5,2,6,3,'20150613 16:57:57.393',N'leaving town for 2 weeks. will start new when she returns.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (6,4,7,1,'20150613 17:36:39.183',N'Working on electical routing');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (7,5,8,1,'20150614 11:41:27.907',N'solid starting work');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (8,4,7,1,'20150614 11:52:25.923',N'finished sanding. first day of stain.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (9,3,9,1,'20150614 11:59:30.113',N'Learning to use a table saw. Nice start.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (10,7,2,2,'20150615 11:20:51.737',N'good finished project.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (11,4,7,2,'20150615 11:21:31.723',N'Excellent work.');
GO
INSERT INTO [tblProjectLog] ([plogID],[projID],[userID],[presID],[plogDateTime],[plogNote]) VALUES (12,1,2,1,'20150615 13:08:53.407',N'starting complicated birdhouse design');
GO

What I am trying to do is get a list of the last entries for each user..

When I run this query:

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

I get this (which is ALL the entries from the test data that was entered):

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

A clean synopsis would be like 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

But what I am hoping to get is just the latest entry for each individual user. There are 3 entries for John Thomas (plogIDs 4, 10, 12) and Lia McKenzie (plogIDs 6, 8, 11). For those I would only want the last entry (plogID 12 for John Thomas and plogID 11 for Lia McKenzie). What I would like to see is something like 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.
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.
7    8    5    1    Stephanie Drake    Toolbox 1    In Progress    2015-06-14 11:41:27.907    solid starting work
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.
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

Cleaned up as:

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

I think I have to combine a MAX and a DISTINCT but I keep getting errors. For example, when I tried:

select MAX(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

I received the 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 removed MAX and added a DISTINCT to the plog.userID field it gave an error of Token in error = DISTINCT.

Does anyone have any examples of how to do what I looking for? I hope this post wasn't too long. I tried to include all the data I thought would be needed. Any help would be apprecited very much.

Thanks.

stepson · June 15, 2015, 6:30pm

select plog.plogID, plog.userID, plog.projID, plog.presID, 
     u.userFirstName + ' ' + u.userLastName AS [User], 
    proj.projName, pres.presResult, plog.plogDateTime, plog.plogNote 
    ,ROW_NUMBER() OVER(PARTITION BY plog.userID ORDER BY plog.plogDateTime DESC) AS RN
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

Later edited: I realize that Row_Number is not available in SQL CE.

SQL_Noob · June 15, 2015, 6:46pm

stepson -

First off thank you so much for helping. Unfortunately when I tried running that but it gave me a token error on "OVER":

ErrorCode: -2147467259
[SQL Server Compact ADO.NET Data Provider]
HResult: -2147217900, NativeError: 25501
ErrorMessage: There was an error parsing the query. [ Token line number = 4,Token line offset = 19,Token in error = OVER ]

SQL_Noob · June 15, 2015, 7:03pm

@stepson -

THANK YOU!!! I finally got it. I had to remove the line that started with ,ROW_NUMBER. Doing that gave me this:

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

which in turn gave me the exact results I was looking for. Thank you so very much. For days I've been trying to figure that out. I don't know exactly what is happening in that query (with the subquery etc) but that gives exactly what I needed. thank you again.