Maximum value from table

[Martin Dashper]

I have a table which is keyed on Field1, Field2 and TiebreakField.
Field1 and 2 can contain duplicates and the tiebreakField only applies
to it's own set of duplicates ie. each set of duplicates in Field1 and
2 may have different range of tiebreak values (I know it sounds
complicated, but the tiebreak values are generated externally)

For example:
Rec1 Field1 = 100 Field2 = "ABC" TiebreakField = 35
Rec2 Field1 = 100 Field2 = "ABC" TiebreakField = 36
Rec3 Field1 = 200 Field2 = "XYZ" TiebreakField = 20
Rec4 Field1 = 200 Field2 = "XYZ" TiebreakField = 21

What I want is a SQL query which will select records where the
tiebreakField has the maximum value. Hence, in the above example, I
want to select Rec2 and Rec4

Martin Dashper

 

[John Nurick]

Hi Martin,

Something like this should do the job:

SELECT FIRST(Field1), FIRST(Field2), MAX(TieBreakField)
FROM MartinsTable
GROUP BY Field1, Field2
HAVING Count(1) > 1
;

Omit the HAVING clause if you want to retrieve "singleton" records as well
as ones that have duplicates.

 

[Martin Dashper]

Many tahnks, I'll give that a whirl.

Martin

Hi Martin,

Something like this should do the job:

SELECT FIRST(Field1), FIRST(Field2), MAX(TieBreakField)
FROM MartinsTable
GROUP BY Field1, Field2
HAVING Count(1) > 1
;

Omit the HAVING clause if you want to retrieve "singleton" records as well
as ones that have duplicates.

 

[Martin Dashper]

OK, It's getting there, but There are still problems.
Perhaps I should have said before that there is a further field in the
table (not indexed). So to modify the example I gave:

Rec1 Field1 = 100 Field2 = "ABC" TiebreakField = 35 Field4 = "Pass"
Rec2 Field1 = 100 Field2 = "ABC" TiebreakField = 36 Field4 = "Pass"

Rec3 Field1 = 200 Field2 = "XYZ" TiebreakField = 20 Field4 = "Fail"
Rec4 Field1 = 200 Field2 = "XYZ" TiebreakField = 21 Field4 = "Pass"

Rec5 Field1 = 300 Field2 = "ABC" TiebreakField = 40 Field4 = "Pass"
Rec6 Field1 = 300 Field2 = "XYZ" TiebreakField = 41 Field4 = "Pass"

This time l want to select only Rec2 and 4, and 6 however, when I add
Field4 to the GROUP BY clause, which I have to do in order to view
it's data, it selects rec2, 3, 4, 5 and 6 because the Field2 and
Field4 values are different. In fact it selects all records where the
values in any of the GROUP BY fields are different.

Martin

 

[John Nurick]

Yes, that makes a big difference. You need to use a query like the
original to select the records, and then join it to the table in order
to access the fields in those records. Something like

SELECT A.* FROM Martin AS A
INNER JOIN
(SELECT FIRST(Field1) As F1, FIRST(Field2) AS F2,
MAX(TieBreakField) As TF
FROM Martin
GROUP BY Field1, Field2
HAVING Count(1) > 1 ) AS B
ON A.Field1 = B.F1 AND A.Field2 = B.F2 AND A.TieBreakField = B.TF
;

 

[Martin Dashper]

Once again, many thanks for that. One more question. How would I
perform a similar query such that I retrieve those records with the
Max value in Field2 (in practise this is a date field) using the Max
value of TiebreakField as the tiebreaker, eg:

Rec1 Field1 = 100 Field2 = "ABC" TiebreakField = 55 Field4 = "Pass"
Rec2 Field1 = 100 Field2 = "ABC" TiebreakField = 36 Field4 = "Pass"
Rec3 Field1 = 100 Field2 = "XYZ" TiebreakField = 20 Field4 = "Fail"
Rec4 Field1 = 100 Field2 = "XYZ" TiebreakField = 21 Field4 = "Pass"

should retrieve Rec4 only

Martin

 

[John Nurick]

Something like this should do it, though there may be a faster and/or
more elegant way:

SELECT A.* FROM Martin AS A
WHERE A.Field2 = (
SELECT MAX(Field2) FROM Martin
) AND A.TiebreakField = (
SELECT MAX(TieBreakField) FROM Martin
WHERE Field2 = A.Field2
)
;

 

[Martin Dashper]

Thanks. Having finally, I think, got my head around some of the SQL
functions, I came up with:

SELECT A.* FROM Martin AS A
INNER JOIN
(SELECT LAST(Field1) As F1, LAST(Field2) AS F2, LAST(TieBreakField) As TF
FROM Martin
GROUP BY Field1 AS B
ON A.Field1 = B.F1 AND A.Field2 = B.F2 AND A.TieBreakField = B.TF

This appears to work, though if you can spot any potential pitfalls, I
would be grateful to know.

Martin

 

[John Nurick]

Hi Martin,

LAST is definitely a potential pitfall. Quoting from fellow MVP John
Viescas's "Microsoft Access 2003 Inside Out", LAST

"Returns the value for the field from the last row encountered in the
group. Note that the last row might not be the one with the highest
value. It also might not be the row you think is 'last' within the
group. Because Last depends on the actual physical sequence of stored
data, it essentially returns an unpredictable value from within the
group."

After reading that and a couple of other references, I'm really not
certain what your query will return. Either test it very thoroughly (and
re-test after doing things that certain change the physical order of the
records), or use more predictable aggregate functions such as MAX.

Thanks. Having finally, I think, got my head around some of the SQL
functions, I came up with:

SELECT A.* FROM Martin AS A
INNER JOIN
(SELECT LAST(Field1) As F1, LAST(Field2) AS F2, LAST(TieBreakField) As TF
FROM Martin
GROUP BY Field1 AS B
ON A.Field1 = B.F1 AND A.Field2 = B.F2 AND A.TieBreakField = B.TF

This appears to work, though if you can spot any potential pitfalls, I
would be grateful to know.

Martin

 

[Martin Dashper]

Thanks for that, I'll certainly bear it in mind.

Martin