Solved Display max value from two column with all columns (1 Viewer)

[Pop_Access]

I have a table (tbl1) with the following columns:-

col1	col2	col3
7-Aug-21​	1​	Pete​
7-Aug-21​	2​	John​
15-Sep-21​	1​	Don​
15-Sep-21​	1​	Elsa​
11-Oct-21​	2​	Rick​
11-Oct-21​	1​	Betty​
11-Oct-21​	2​	Vera​
11-Oct-21​	1​	Ronald​

With a query or VBA code, I want to reach the records with the highest value in col1 then the highest value in col2 as follows:

col1	col2	col3
11-Oct-21	2	Rick
11-Oct-21	2	Vera

I want to get those records and all of the columns in the records.

Thank you.

 

[arnelgp]

you can use query:

select tbl1.* from tbl1
where [col1] & "/" & [col2] IN
(select dmax("[col1] & '/' & [col2]", "tbl1") from tbl1)

 

[Galaxiom]

you can use query:

select tbl1.* from tbl1
where [col1] & "/" & [col2] IN
(select dmax("[col1] & '/' & [col2]", "tbl1") from tbl1)

I've not seen a Where clause like that before.

It would not be efficient because it is not Sargable, meaning that it cannot use the indexes on the fields.
You should always avoid Where clauses requiring every record to be processed before the comparison.

 

[Pop_Access]

you can use query:

select tbl1.* from tbl1
where [col1] & "/" & [col2] IN
(select dmax("[col1] & '/' & [col2]", "tbl1") from tbl1)

Great Job. Thank you Very Much

 

[Pat Hartman]

Your requirement is not clear and neither is the example. The solution given can be simplified to
Select top 1 col1, col2
from yourtable
Order by col1 desc, col2 desc

What do you want if the max of col1 and the max of col2 are NOT in the same record?

 

[Pop_Access]

Your requirement is not clear and neither is the example. The solution given can be simplified to
Select top 1 col1, col2
from yourtable
Order by col1 desc, col2 desc

What do you want if the max of col1 and the max of col2 are NOT in the same record?

As I mentioned above, I want to reach the records with the highest value in col1 First, then the highest value in col2.

So, the highest value in clo1 was (11-Oct-21). and we have 4 records with this value, now the highest value in clo2 was (2) for 2 records for Rick and Vera. that's what I want.
Hope this description is clear

 

[Pat Hartman]

Hope this description is clear

Then you don't need the solutions with the domain functions. You just need a simple sort and a top 1 predicate to get the record you want.

 

[Pop_Access]

Then you don't need the solutions with the domain functions. You just need a simple sort and a top 1 predicate to get the record you want.

Sorry; I didn't get your point.

 

[Pat Hartman]

Select Top 1 yourtable.* from yourtable
order by col1 desc, col2 desc;

Notice there is no subselect and no domain function. That is the point. Just a simple query that selects one record by ordering the recordset descending on the relevant columns.

 

[Pop_Access]

Select Top 1 yourtable.* from yourtable
order by col1 desc, col2 desc;

Notice there is no subselect and no domain function. That is the point. Just a simple query that selects one record by ordering the recordset descending on the relevant columns.

Thank you for your comments, you're right, I'm learning new ideas from you all.
Sorry I'm not a programming expert but it's a hobby.

 

[Pat Hartman]

You're welcome. Most people here are not experts either. It is a place to learn best practices and usually more fun than reading a book.