[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Tricky Query

Posted on 2006-05-19
Medium Priority
Last Modified: 2008-03-10
Below is a data set from MS Access:

Cert#                      Name                      Coverage                      Pay't Mode                    indicator
3503490991      AHUVA WOODS      DRUG ONLY MEMBER      Annual      17
3493487341      ALAIN BELLEHUMEUR      INCOME PROTECTION 120 DAY      Monthly      24
3493487341      ALAIN BELLEHUMEUR      COLA RIDER 120 DAY      Monthly      10
3493487341      ALAIN BELLEHUMEUR      EXTENDED HEALTH      Monthly      22
3493487340      ALAIN BELLEHUMEUR      TERM LIFE      Monthly      1
3493487340      ALAIN BELLEHUMEUR      PERSONAL ACCIDENT      Monthly      5
3493487481      ALAIN BOILY      OFFICE OVERHEAD 30DAY/12MON      Monthly      23
3493487481      ALAIN BOILY      INCOME PROTECTION 30 DAY      Monthly      26
3493488090      ALAIN DUVAL      PERSONAL ACCIDENT FAMILY      Monthly      3
3493488091      ALAIN DUVAL      INCOME PROTECTION 30 DAY      Monthly      26
3493488090      ALAIN DUVAL      TERM LIFE      Monthly      1
3493488091      ALAIN DUVAL      EXTENDED HEALTH COUPLE      Monthly      21
3493492620      ALAIN LAFLEUR      TERM LIFE      Monthly      1
3493889501      ALAIN LAFLEUR      EXTENDED HEALTH COUPLE      Monthly      21
3503493151      ALAIN LAFOND      DRUG ONLY FAMILY      Monthly      18
3503665361      ALAIN LEMAY      DRUG ONLY FAMILY      Monthly      18
3503490761      ALAIN LEPAGE      DRUG ONLY MEMBER      Annual      17
3503699271      ALAIN M LETUVE      DRUG ONLY FAMILY      Annual      18
3493488981      ALAN MACNAUGHTON      DENTAL RIDER FAMILY      Monthly      15
3493488980      ALAN MACNAUGHTON      PERSONAL ACCIDENT FAMILY      Monthly      3
3493488981      ALAN MACNAUGHTON      INCOME PROTECTION 120 DAY      Monthly      24
3493488981      ALAN MACNAUGHTON      EXTENDED HEALTH FAMILY      Monthly      20
3493488980      ALAN MACNAUGHTON      TERM LIFE      Monthly      1


This data has the same people with different cert#s. The only difference is the indicator, last column.  I want to pull out distinct records that have the highest indicator.  

So for example:

3493488981-----ALAN MACNAUGHTON-----DENTAL RIDER FAMILY---Monthly---15
3493488981-----ALAN MACNAUGHTON-----INCOME PROTECTION----Monthly---24
3493488981-----ALAN MACNAUGHTON-----EXTENDED HEALTH F-----Monthly---20
3493488980-----ALAN MACNAUGHTON-----TERM LIFE-----------------Monthly---1

I only want the record that says 24.

This is kind of urgent...

Question by:Lucas
  • 2
  • 2
  • 2
LVL 65

Expert Comment

ID: 16721409
something like this perhaps?

select a.*
from table a
where indicator = (select max(indicator) from table where name = a.name)

helps if name field is indexed

LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 16721467
How about:

Select a.[Cert#], a.[Name], a.Coverage, A.[Pay'tMode], a.Indicator from myTable AS a
INNER JOIN myTable AS b ON a.[Name] = b.[Name] Having a.Indicator = Max(b.Indicator);
LVL 13

Author Comment

ID: 16721507
Rockiroads' query returns all the records with an indicator of 27.
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 13

Author Comment

ID: 16721547
GRayL, you're *BANG* on the money....

LVL 65

Expert Comment

ID: 16721576
did mine return all records?

did u remember to put table aliases?
as we are matching by name
LVL 44

Expert Comment

ID: 16721650
Thanks, glad I could help.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question