New user and very new at sql. Sorry now if my language is not correct. I am building a little program to try and learn more about sql and visual basic. I am trying to build a simple table (4 fields) that will track student status changes. Simple table that will insert a new record any time student change status. I have seen some posts on here where the user posts code to create and populate the table. I apologize I don't know how to do that.
It is a SQL Server Compact 3.5 database.
The table is very basic:
stdstsID int primary key
stdID int
sttsID int
stdstsDateTime datetime
Some example data that will eventually fill the table:
stdstsID - stdID - sttsID - stdstsDateTime
1 - 2 - 2 - 2016-01-16 13:10:27
2 - 4 - 2 - 2016-01-16 13:13:21
3 - 5 - 2 - 2016-01-16 13:17:00
4 - 3 - 2 - 2016-01-16 13:19:04
5 - 2 - 1 - 2016-01-16 13:23:55
6 - 3 - 1 - 2016-01-16 13:29:23
7 - 7 - 2 - 2016-01-16 13:33:42
8 - 8 - 2 - 2016-01-16 13:40:01
9 - 4 - 1 - 2016-01-16 13:42:20
10 - 5 - 3 - 2016-01-16 13:45:30
What I am trying to get is the most recent (highest stdstsID?) for each studentID for a given day. So with the example data here, what I would hope to get is:
stdstsID - stdID - sttsID - stdstsDateTime
5 - 2 - 1 - 2016-01-16 13:23:55
6 - 3 - 1 - 2016-01-16 13:29:23
7 - 7 - 2 - 2016-01-16 13:33:42
8 - 8 - 2 - 2016-01-16 13:40:01
9 - 4 - 1 - 2016-01-16 13:42:20
10 - 5 - 3 - 2016-01-16 13:45:30
I tried this:
select distinct(stdID), sttsID, stdstsDateTime from tblStudentStatus where convert(datetime, convert(nvarchar, stdstsDateTime, 101)) = '1/16/2016' order by stdID asc, stdstsDateTime desc, sttsID asc
I was think that the distinct would give me only one listing for each stntID. But it didn't. Instead I got these results:
2 - 1 - 2016-01-16 13:23:55
2 - 2 - 2016-01-16 13:10:27
3 - 1 - 2016-01-16 13:29:23
3 - 2 - 2016-01-16 13:19:04
4 - 1 - 2016-01-16 13:42:20
4 - 2 - 2016-01-16 13:13:21
5 - 3 - 2016-01-16 13:45:30
5 - 2 - 2016-01-16 13:17:00
7 - 2 - 2016-01-16 13:33:42
8 - 2 - 2016-01-16 13:40:01
As you can see I'm getting duplicate entries for certain students. So the unique part didn't work. How would I write the query so that I get the results that I listed above (where there were six records returned)?
Actually I do not need the stdstsID field so that could be left out. I don't really know if that field is required in the database because this is only going to be a records table and with the DateTime field I can always search by that if necessary. Would it be a good idea to remove the primary key field being that I don't really need it for anything?
Please excuse my lack of understanding/proper language for sql work. I am new to it and pretty confused.