Thread: Aggregates

Ok, I know I'm going to feel really silly when someone answers this because it should be very basic, but I'm at a loss at this point on how to select a record based on a min/max value. I realize I can use a Having clause, but it doesn't produce what I want.

Take the following data:

Create Table Customer (CustomerId int primary key, DateofBirth int);
Insert Into Customer (CustomerId, DateOfBirth)
Values (1, 19010801);
Insert Into Customer (CustomerId, DateOfBirth)
Values (2, 19100101);
Insert Into Customer (CustomerId, DateOfBirth)
Values (3, 19201201);

Select * from Customer

Results:
CustomerId DateOfBirth
1 19010801
2 19100101
3 19201201

Ok, so if I want to know the customerid of the person with the min or max date of birth, how would I go about it?

I can write:

Select customerid, Min(dateofbirth), Max(dateofbirth)
From Customer
Group by customerid


but it just yields all records grouped by customerid. What if I wanted to see just the customerid and the dateofbirth of the min or max?

Thanks!

See! I told you I'd feel stupid Thanks Wolffy..as usual

I'm not! The application is I think the database that we use was originally created in the mid 90's and they never made the leap to start using datetime fields. Took me about 6 months of racking my brains to get past all the problems with that. As we've discussed on numerous occasions, I can get onto SQL Server 2005, but most of the work I do needs to be done client-side on a limited query writing tool. One of the tools doesn't even recognize datetime unless it's converted to a character...so I have to do some goofy conversions thusly:

EX:

Declare @Date INT
Set @Date=20100101

SELECT CONVERT(CHAR(8), Convert(DATETIME, Convert(char(8), @Date,10)),10)

Results:
01-01-10

If I Just say:

Declare @Date INT
Set @Date=20100101

SELECT Convert(DATETIME, Convert(char(8), @Date,10))

The column disappears and the log says that it doesn't recognize the datetime data type.

My life is full of workarounds!

I agree! I actually had a chance to meet with the developer of the application about a year ago and picked his brain on all the weirdness in the application/db.

Very interesting stuff...at the very least, I've had real life experience of things to avoid!