Re: query question for sql experts
On Wed, 21 Jan 1998, Leslie Tseng wrote:
} Thanks Art.
}
} But...
} Sorry, I gave a bad example. Let me try another example:
}
}
} TableA contains:
}
} EmpID GroupID CrDate
}
} 22 4 1/20/98
} 20 3 1/19/98
} 25 4 1/18/98
} 25 6 1/11/98
} 20 5 1/16/98
} 22 3 1/15/98
} ...
} ...
} ...
} Expected query output:
} (Group by EmpID and order by lastest crdate first
} within each EmpID)
} EmpID GroupID CrDate
} 22 4 1/20/98 <- latest in crdate for a emp
} 22 3 1/15/98
} 20 3 1/19/98 <- second latest
} 20 5 1/16/98
} 25 4 1/18/98 <- 3rd latest
} 25 6 1/11/98
} 12 1/17/98 <- 4th latest
} 12 1/14/98
} 30 1/14/98 <- 5th latest
} 30 1/13/98
} 30 1/12/98
Ah I thought that looked too easy. Hmmmm. OK, this requires a temp
table:
select a.EmpID, a.GroupID, a.CrDate, MAX(b.CrDate) maxdate
from TableA a, TableA b
where a.EmpID = b.EmpID
group by 1, 2, 3
into temp fred_flintstone;
select *
from fred_flintstone
order by 4, 1, 3;
This returns the extra column (maxdate) from the temp table but there
is no other what that I can think of.
Art S. Kagel, kagel@bloomberg.com