Query to select one row from each group ?

[Richard]

In a manufacturing document control situation, a procedure revision
table named [tblProcedures] has principal columns [Name] and
[Revision] and satellite data columns [Date], [Person], [Comments]. A
unique index [Name] and [Revision] to ensure each procedure name has
only unique revision numbers.

A form has a listbox for selecting a procedure and revision for
browsing, and a checkbox 'Show all'.
When ShowAll is true the listbox rowsource can be [tblProcedures].
When ShowAll is false the listbox should only show each procedures
highest revision.

foo replaces date in this sample data

name rev foo
A 1 U
A 2 V
A 3 W
B 1 X
B 2 Y
B 3 Z
B 4 K

want

A 3 W
B 4 K

tried
select * from procs group by name having rev=max(rev)
but access97 does not like.

[tina]

try using a Totals query; in query Design view, set the Total: row in the
Rev field to Max.

hth
"Richard" <ri************ @devenezia.comw rote in message
news:11******** **************@ m58g2000cwm.goo glegroups.com.. .

In a manufacturing document control situation, a procedure revision
table named [tblProcedures] has principal columns [Name] and
[Revision] and satellite data columns [Date], [Person], [Comments]. A
unique index [Name] and [Revision] to ensure each procedure name has
only unique revision numbers.

A form has a listbox for selecting a procedure and revision for
browsing, and a checkbox 'Show all'.
When ShowAll is true the listbox rowsource can be [tblProcedures].
When ShowAll is false the listbox should only show each procedures
highest revision.

foo replaces date in this sample data

name rev foo
A 1 U
A 2 V
A 3 W
B 1 X
B 2 Y
B 3 Z
B 4 K

want

A 3 W
B 4 K

tried
select * from procs group by name having rev=max(rev)
but access97 does not like.

[Kevin Chambers]

On Feb 4, 4:40 am, "Richard" <richard.goo... @devenezia.comw rote:

In a manufacturing document control situation, a procedure revision
table named [tblProcedures] has principal columns [Name] and
[Revision] and satellite data columns [Date], [Person], [Comments]. A
unique index [Name] and [Revision] to ensure each procedure name has
only unique revision numbers.

A form has a listbox for selecting a procedure and revision for
browsing, and a checkbox 'Show all'.
When ShowAll is true the listbox rowsource can be [tblProcedures].
When ShowAll is false the listbox should only show each procedures
highest revision.

foo replaces date in this sample data

name rev foo
A 1 U
A 2 V
A 3 W
B 1 X
B 2 Y
B 3 Z
B 4 K

want

A 3 W
B 4 K

tried
select * from procs group by name having rev=max(rev)
but access97 does not like.

Try:

SELECT * FROM procs AS p
WHERE NOT EXISTS (
SELECT *
FROM procs AS p2
WHERE name=p.name AND rev>p.rev
)

[Richard]

On Feb 4, 10:12 pm, "Kevin Chambers" <KBChamb...@gma il.comwrote:

Try:

SELECT * FROM procs AS p
WHERE NOT EXISTS (
SELECT *
FROM procs AS p2
WHERE name=p.name AND rev>p.rev
)

Thanks Kevin.

Is the where not exists fundamentally better than inner joining a
subselect, such as:

SELECT * FROM procs
INNER JOIN
[ SELECT name, max(rev) AS maxRev FROM procs GROUP BY name ]. AS
sqNavel
ON
procs.name = sqNavel.name
AND
procs.rev = sqNavel.maxRev

[Kevin Chambers]

On Feb 4, 8:41 pm, "Richard" <richard.goo... @devenezia.comw rote:

On Feb 4, 10:12 pm, "Kevin Chambers" <KBChamb...@gma il.comwrote:

Try:

SELECT * FROM procs AS p
WHERE NOT EXISTS (
SELECT *
FROM procs AS p2
WHERE name=p.name AND rev>p.rev
)

Thanks Kevin.

Is the where not exists fundamentally better than inner joining a
subselect, such as:

SELECT * FROM procs
INNER JOIN
[ SELECT name, max(rev) AS maxRev FROM procs GROUP BY name ]. AS
sqNavel
ON
procs.name = sqNavel.name
AND
procs.rev = sqNavel.maxRev

Hmm, that's a good question. I guess it depends on what you're using
the query for. Since you're using it for a list box only, speed is
probably the most important consideration. Having the subselect in
the FROM clause as you do may be faster in Access since Access seems
to be able to optimize that kind of query structure much better, but
you'd want to test them both to be sure.

What I like about using NOT EXISTS in the FROM clause is that there's
no question about the recordset's updatability. There's only one
table in the FROM clause, so it's a legitimate updatable view in SQL
Server. And from a more stylistic standpoint, I find the SQL a wee
bit cleaner and easier to read. It's clear to me when I come back to
a query I've written with NOT EXISTS that my intention was filtering,
not grouping.

KC