Software

Ask something
Question Locked

SQL subquery woes.

0 helpful
3 replies

by tbrown

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Let me preface this by saying I'm a poweruser sort of fella with a BA in anthropology. I basically fell into a BI/IS/DBA/report development role in addition to other random things.

I basically had to pick up a working knowledge of SQL, and I can get most tasks I need done fairly well, almost all of which result in data sets that I process through Crystal Reports '08.

My current predicament is trying to figure out how to get the value of an int field for the maximum datetime value for a combination of two other identifiers, excluding all rows where the field value is 10, and have that int return for all rows of the two-ID combo. As that probably makes limited to zero sense, here's what I want in terms of fields:

CandidateID, JobID, Status, DateTime, Stage, FinalStageDateTime, FinalStage

Status is an unrelated char field describing the activity. I'm not sure if this would be best served by a subquery or some sort of join I haven't tried yet. The final goal is to be able to sort CandidateIDs within a JobID group based on their last stage before being disqualified (stage 10).

I apologize ahead of time if the explanation is a bit convoluted, I'm an admitted n00b at this stuff and this particular project is turning my hair gray early.

3 replies

by tony hopkinson

If I'm getting it you want to derive FinalStageDateTime and FinalStage ?

Select m.CandidateID,m.JobID,Status,[DateTime],Stage,FinalStageDateTime,FinalStage)
From mytable m,
(Select CandidateID,
JobID,
Max([DateTime]) as FinalStageDateTime,
Max(Stage) as FinalStage From MyTable
Where Stage <> 10
Group By CandidateID,JobID) maxes
Where m.CandidateID = maxes.CandidateID and m.JobID = maxes.JobID

Subquery with a join. Sneaky trick, haven't parsed it and some DBMS's will snivel about it, but the technique is sound.

Some other tricks would be a table function to return max date and one for max stage.
Put the inner fella in a view or a temporary table.

Depends on the volume of data and how often you need to run it. The quick route is probably a View and then join to it, especially if you can index it.

HtHs

This discussion is closed to new replies.