Find a Min Value and include add Fields 2

[Lost500]

Hi all,
I am running a query where i would like to find a minimum date in a group of records and also include some other fields with it. the problem is when i add more fields to my query they are defaulted to group by so, where i want to see the Unit name for the record with the min date, i get the min date for each Unit record. so if i have a data set of:

Unit name: Date:
Me 1/2/2000
You 2/2/2000


The result i'm looking for would be just the record with Me and 1/2/2000 but when i add the unit name to the query it adds a group by for it and i get both records i.e. giving me the min date for each unit which i understand why but i'm looking for the method to get to the min date value and some additional information. I know i'm missing something thanks for the help.

Thanks
Lost

 

[lespaul]

I think what you want is something like this:

SELECT all other fields you want, MinimumDate
FROM TableName
INNER JOIN (SELECT UnitName, Min([Date]) As MinimumDate FROM tableName) A On TableName.UnitName = A.UnitName and TableName.DateField = A.MinimumDate

Leslie

 

[Lost500]

Thanks lespaul so i have a stupid question, what does A stand for? thanks again i think we're getting close.

 

[lespaul]

'A' is an alias for the subquery in the INNER JOIN, just makes it easier when there are long table names. Here's the query with 2 aliases, I added one for TableName 'T':

SELECT all other fields you want, MinimumDate
FROM TableName [b]T[/b]
INNER JOIN (SELECT UnitName, Min([Date]) As MinimumDate FROM tableName) A On [b]T[/b].UnitName = A.UnitName and [b]T[/b].DateField = A.MinimumDate

Leslie

 

[lespaul]

and I should add that when you are using a subquery like this you HAVE to use an alias

 

[Lost500]

Thanks for the quick response and thanks for the education! ok so it turns out we're talking about TractName not UnitName but here is the code i tried:

SELECT dbo_tblTitleInfo.TitleType, dbo_tblTitleInfo.Author, dbo_tblTitleInfo.UnitTractNumber, MinimumDate
FROM dbo_tblTitleInfo T
INNER JOIN (SELECT TractName, Min([dbo_tblTitleInfo.Date]) As MinimumDate FROM dbo_tblTitleInfo) A On T.TractName = A.TractName and T.Date = A.MinimumDate;

and i get the error "You tried to execute a query that does not include the specified expression 'TractName' as a part of an aggreagate function."

TractName is a field that i would like the MIN date record for each TractName along with Author, TitleType and UnitTractNumber, so i'm doing something wrong, please help.

also am i supose to add MinimumDate to my Select statement like i have it? it isn't a field on a table.

Thanks

 

[PHV]

...
INNER JOIN (SELECT TractName, Min([Date]) As MinimumDate FROM dbo_tblTitleInfo [!]GROUP BY TractName[/!]) A
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lost500]

Thanks PHV

so i inserted the group by and now when i run the query it prompts me for parameter values for each of the additional fields i added i.e.

SELECT [red]dbo_tblTitleInfo.TitleType, dbo_tblTitleInfo.Author, dbo_tblTitleInfo.UnitTractNumber[/red], MinimumDate
FROM dbo_tblTitleInfo T
INNER JOIN (SELECT TractName, Min([dbo_tblTitleInfo.Date]) As MinimumDate FROM dbo_tblTitleInfo) A On T.TractName = A.TractName and T.Date = A.MinimumDate;

also the query made my additional fields expressions, adding AS Expr1 at the end of my statements, so after running the query my SQL now looks like:

SELECT dbo_tblTitleInfo.TitleType [red]AS Expr1[/red], dbo_tblTitleInfo.Author [red]AS Expr2[/red], dbo_tblTitleInfo.UnitTractNumber [red]AS Expr3[/red], A.MinimumDate
FROM dbo_tblTitleInfo AS T INNER JOIN (SELECT TractName, Min([dbo_tblTitleInfo.Date]) As MinimumDate FROM dbo_tblTitleInfo GROUP BY TractName)  AS A ON (T.Date = A.MinimumDate) AND (T.TractName = A.TractName);

I am wanting to include those fields with the records that are the Min date for each tract record. I don't know where i'm going wrong though i have no idea how sub queries work.

Thanks!

 

[PHV]

SELECT T.TitleType, T.Author, T.UnitTractNumber, A.MinimumDate
FROM dbo_tblTitleInfo T INNER JOIN (
SELECT TractName,Min([Date]) As MinimumDate FROM dbo_tblTitleInfo GROUP BY TractName
) A ON T.TractName = A.TractName AND T.Date = A.MinimumDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lost500]

wow i hope ya'll are getting paid for this, it works perfect, thanks for leading me all the way through ya'll really are something special.

thanks again!

 

[lespaul]

PHV is the SQL God....I regularly sacrifice in his name to be as good as he is someday!

Leslie