By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,399 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

sql returning max of a field?

P: n/a
Hello,

I have a table with 2 fields, Title and Version.

some sample records would be:

Title1 1
Title1 2
Title1 3
Title2 1
Title2 2
how can I write the select statement to return the Title and
latest/highest version ?

the resulting table I am hoping for would be:
Title1 3
Title2 2

thanks.

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Create a query that includes both fields. Click on the Sigma (looks like E)
button on the toolbar at the top of the screen. Under Version, change Group
By to Max. Click on the View button on the far left of the toolbar at the
top of the screen and change to SQL. You will now see the Select staement
you want.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Eych" <ey******@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Hello,

I have a table with 2 fields, Title and Version.

some sample records would be:

Title1 1
Title1 2
Title1 3
Title2 1
Title2 2
how can I write the select statement to return the Title and
latest/highest version ?

the resulting table I am hoping for would be:
Title1 3
Title2 2

thanks.

Nov 13 '05 #2

P: n/a
got it...

thanks

Nov 13 '05 #3

P: n/a
I followed your suggestion on another table which also has Title and
Version fields, but it also has about 30 other fields.

after I let Access create the query, it turned out to be a very large
select statement.

It had, "Select title, max(version) as maxversion, field3, field4
....(all the way to field30) from Table1 group by title, field3, field4,
....(all the way to field30)"

it works, but is there a way to shrink the statement where I can get
the complete record for the latest version of each title?

Nov 13 '05 #4

P: n/a
In query design view, pull down the asterisk in the first field and do the
max version thing in the second field.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Eych" <ey******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I followed your suggestion on another table which also has Title and
Version fields, but it also has about 30 other fields.

after I let Access create the query, it turned out to be a very large
select statement.

It had, "Select title, max(version) as maxversion, field3, field4
...(all the way to field30) from Table1 group by title, field3, field4,
...(all the way to field30)"

it works, but is there a way to shrink the statement where I can get
the complete record for the latest version of each title?

Nov 13 '05 #5

P: n/a
thanks again for your help...

When I tried to click the Sigma, I got the following message:

"You can't calculate totals on the asterisk (*).
Because the asterisk represents all the fields in the table, you can't
calculate totals on it."

the sql I want to run is:
"SELECT Table1.*, max(Table1.Version) as maxversion from Table1"
but this always errors out...

Nov 13 '05 #6

P: n/a
thanks again for your help...

When I tried to click the Sigma, I got the following message:

"You can't calculate totals on the asterisk (*).
Because the asterisk represents all the fields in the table, you can't
calculate totals on it."

the sql I want to run is:
"SELECT Table1.*, max(Table1.Version) as maxversion from Table1"
but this always errors out...

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.