Posted: 7/18/2012 7:56:20 AM EDT
|
My SQL-fu is a bit rusty. I have the following table Employee#, Date,(other fields) 1, 1/1/2000, 1, 1/2/2000, 1, 1/3/2000, 2, 1/1/2000, 2, 1/3/2000, 2, 1/4/2000, I want to select the employee # and the most recent date for that employee. The results should look something like this 1, 1/3/2000,(other fields) 2, 1/4/2000,(other fields) I don't think Select Distinct will work here. |
|
Assuming you want one row for each employee and that row contains the "other fields" for the one record that has the max date for that employee.
You did not say what db system you are using. The following should get you close to what you want using MS SQL Server. select a.* from employee a inner join ( select b.employeeID , max(b.empdate) as empDate from employee b group by b.employeeID ) c on c.employeeID = a.employeeID and c.empDate = a.empdate You could also use Row_number() and "partition over". |
|
Quoted:
SELECT EmployeId,MAX(DateFieldName) FROM TableName GROUP BY EmployeId; This works. I independently came up with something similar:
Works in PostgreSQL. PS, Why does ar15.com reformat text in [pre] and blocks?
|
|
Quoted:
Quoted:
SELECT EmployeId,MAX(DateFieldName) FROM TableName GROUP BY EmployeId; This works. I independently came up with something similar: ruertar=# CREATE TABLE employee ( id integer, infraction date );CREATE TABLEruertar=# SELECT * FROM employee; id | infraction ––––+–––––––––––– 1 | 1975-05-11 1 | 1935-05-11 1 | 1985-05-11 1 | 1985-03-11 1 | 1985-03-14 2 | 1999-01-14 2 | 2009-01-14(7 rows)ruertar=# SELECT id, max(infraction) FROM employee GROUP BY id; id | max ––––+–––––––––––– 1 | 1985-05-11 2 | 2009-01-14(2 rows) Works in PostgreSQL. PS, Why does ar15.com reformat text in [pre] and blocks?
But you will have to take the results from that query and do an inner join back against the first table since group by won't allow you to put in any other fields. |
|
Quoted:
But you will have to take the results from that query and do an inner join back against the first table since group by won't allow you to put in any other fields. Yes –– this is true. I assume the OP would take any steps to utilize the results of this query (like joining). Also, great user name. :^) I did Sun/SPARC development until two years ago. |
|
Quoted:
Quoted:
But you will have to take the results from that query and do an inner join back against the first table since group by won't allow you to put in any other fields. Yes –– this is true. I assume the OP would take any steps to utilize the results of this query (like joining). Also, great user name. :^) I did Sun/SPARC development until two years ago. I do love how few people know what it is. Funniest thing is my wife was trying to figure out a username somewhere so now she is c0t0d0s2 |