Select most recent records for a given day?

Transact-SQL
1

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.

2

I doubt Compact 3.5 support row_number(), so the solution here uses a conventional method rather than row_number()

First you need to identify these records

SELECT  stdID,
	max_stdstsID = max(stdstsID)
FROM    tblStudentStatus
GROUP BY stdID

Then you join it back to the table

SELECT s.*
FROM   tblStudentStatus s
INNER JOIN (
	SELECT  stdID,
		max_stdstsID = max(stdstsID)
	FROM    tblStudentStatus
	GROUP BY stdID
	) AS m
	ON  s.stdID = m.stdID
	AND s.stdstsID = m.max_stdstsID
1 Like
3

khtan - thank you for the reply. I'm getting a parsing error on this line:

max_stdstsID = max(stdstsID)

This is the error message my sql gui is giving me:

ErrorMessage: There was an error parsing the query. [ Token line number = 27,Token line offset = 25,Token in error = = ]

and line number 27 is the one I copied above.

Is there another way to get the separate list of IDs?

4

Replace this:

with this:

max(stdstsID) as max_stdstsID
1 Like
5

khtan and bitsmed - Thank you both very much. That is giving me exactly what I was looking for. Thank you again.

6

Another method

SELECT s.*
FROM tblStudentStatus s
WHERE s.stdstsID =
(
SELECT
max(t.stdstsID)
FROM tblStudentStatus as t
where t.stdID=s.stdID
)