Filtering out unwanted entries from table

Question

I have a table (really, a result of a query) that has the following attributes:

Continent
Group_name
job_name
status
run_time

For a given job, there can be a status of 'running', 'completed', or 'failed'. Sometimes, a job will fail, and then run again. Or a job my be running forever, and another job will start. So in some cases, a job may have two entries with 2 different statuses (ex. 'failed' and 'complete'). In these cases, I only want one entry. Anytime one of the entries is 'complete', I would want to eliminate the other entry.

How would I go about doing this? How do I conditionally eliminate 'overlapping' entries?

Edit:

One option I thought of was joining the table on itself (using the WITH clause since my table is actually a query). However I'm not sure how I can get that to work, or what conditions to set so that only one of the two wanted entries are returned.

Answer 1

A great way to do this would be to normalize your table. Take your table and change it to look like this:

Continent
Group_name
job_name
run_time
statusID_FK

Next, make the status ID a foreign key linked to a Status table:

StatusID
Status_Description

(Status description could be called whatever. Values would be "complete", etc)

Then, all you would need to do would be to set up a 1-Many relationship between the tables.

Once that is done, add logic in your application code which will check at an interval you specify (perhaps whenever an update is performed) if an entry exists which is "complete" and can be removed based on your criteria.

If it is found, just use a SQL stored procedure to delete it from the database.

At least, that's what I would do. Hope this helps!