Repeated records filter

[Mc Brain]

Hi everybody. I need your expertise to answer me this. It could probably be very easy, but have no clue how to do it.

This is the problem: I will try to explain it. I have a recordset which has the records of all the pupils "enrolled" to some curse (e.g. "Mathematics") However, this pupils could appear repeated. For example if he was "erased" because of several absences to the curse (or different causes) and "re-enrolled" later with a justification for his absences. (never mind this... just to catch the idea). The real problem is how do you have to write the SELECT statement to be able to get the list of the pupils still who are still enrolled. I have a field with the date of each new record, so I would need to get the "newest record" for each pupil in the curse. Got the point??

P.S.: Just in case, I'm using SQL Server 7 and VB 5

[JHausmann]

what about:

select student, max(date) from table
group by student

[Mc Brain]

I really don't understand much of SQL, but I can't make your tip work. It works fine as you wrote it, but how should it be written if I need some fields from other recordsets??

[JHausmann]

You could either union the query I gave you to another or you could select the information into a temp table and join that table to whatever table(s) contain the other data you're missing.

The trouble with the max function is that it is an aggregrate function that requires the groupby (or having) clause. It tends to make queries quite convoluted when you want to collect lots of data

If you need further help, post the table(s) and fields and what you want the query results to be.

[Mc Brain]

The tables are:

TABLE inscrip:
co_mate
co_curso
fe_mov
(others fields I don't remember now (I'm at home))
co_control
co_estado

TABLE dpalumno:
nu_lega
no_alum
(many other fields)
co_control
co_estado


and the query I would like you to "translate" is something like this:

SELECT i.nu_lega, no_alum, co_curso, fe_mov, i.co_control FROM inscrip i, dpalumno a WHERE co_mate= '950427' AND co_curso = 'R3061' AND i.nu_lega = a.nu_lega

This the query as I remember it (more or less), never mind the complete statement just the idea. What I'm asking in the statement is to get some of the fields of the recordset for the '950427' subject and the curse 'R3061', besides I'm askig to get the name (no_alum) of the student which is in another recordset (dpalumno). However, this query, would bring me all the records (not the "newest" for each student). Another thing: fe_mov is the datetime field that contains the date where it was added to the recordset.

Thanks a lot for your help.

[JHausmann]

You might try this although I'm not sure it's going to work, I have no place to test it :) :


SELECT i.nu_lega, no_alum, co_curso, fe_mov, i.co_control FROM inscrip i, dpalumno a WHERE co_mate= '950427' AND co_curso = 'R3061' AND i.nu_lega = a.nu_lega and fe_mov=(select max(fe_mov) from inscrip having co_mate= '950427' and co_curso = 'R3061' )

[Edneeis]

It might be easier to just make an 'active' field and then only query the 'active'=true students.

[Mc Brain]

Ok... I will try on monday 'cause I don't have where to probe it right now, either. I let you know...

[JHausmann]

Originally posted by Edneeis
It might be easier to just make an 'active' field and then only query the 'active'=true students.

Might be but there would be a different set of problems. You'd need to have logic to turn on/off the flag and the database would need to be set. In the end it might be more work. But the only one who knows is the one that has to do it... :)

[Mc Brain]

It didn't work. The tables were this ways:

TABLE inscrip:
nu_lega
co_mate
co_curso
fe_mov
co_control
co_estado

TABLE dpalumno:
nu_lega
no_alum
(many other fields such as address, and all that stuff)
co_control
co_estado

And this is the query with the fields I need:

SELECT i.nu_lega, a.no_alum, co_mate, co_curso, fe_mov, i.co_estado FROM inscrip i, dpalumno a WHERE co_mate = '950427' AND co_curso = 'R3061' AND i.nu_lega = a.nu_lega

but this brings me all the records not the "newest one". The query you propose, post-edited to correct some errors is:

SELECT i.nu_lega, no_alum, co_curso, fe_mov, i.co_control FROM inscrip i, dpalumno a WHERE co_mate= '950427' AND co_curso = 'R3061' AND i.nu_lega = a.nu_lega and fe_mov=(select max(fe_mov) from inscrip having i.co_mate= '950427' and i.co_curso = 'R3061')

but this brings me only one record (one student). I mean, it brings the newest record at all, not the newest for each student.

[JHausmann]

What dbms are you using Access or SQL Server?

[Mc Brain]

I'm using SQL Server 7.0

[JHausmann]

Good. You'll need to create a stored procedure that creates the temproray table that will be linked to your new query (all will be provided). The only thing I'm not sure of is if you can drop a table and build it again in the same stored procedure. You may need to modify it accordingly.
Once you've got it working you just need to make sure it runs immediately before the query (although, if nothing's changed, you could skip the stored procedure).

[stored procedure]
if exists (select * from sysobjects where id = object_id('dbo.cpCreateTemp') and sysstat & 0xf = 4)
drop procedure dbo.cpCreateTemp
GO

/* Stored procedure to build temp table table */


CREATE PROCEDURE cp_cpCreateTemp
AS

Drop table ##TempStudent
GO

select nu_lega, max(fe_mov)
insert into ##TempStudent
from inscrip
group by nu_lega

GO

GRANT EXECUTE ON dbo.cpCreateTemp TO all
GO
[end stored procedure]

[new query]
SELECT i.nu_lega, no_alum, co_curso, i.fe_mov, i.co_control FROM inscrip i, dpalumno a, ##TempStudent t WHERE co_mate= '950427' AND co_curso = 'R3061' AND i.nu_lega = a.nu_lega and i.fe_mov=t.fe_mov and i.nu_lega=t.nu_lega
[end new query]