Show only 1 of each matching record in query

[magmike]

I have created a query that will result in many duplicate listings. The ID number, however is different on each listing. I only want the query to show the record of each company that has the largest id number.

Example:

Turn this:
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1211 -- Comp1
1210 -- Comp1
1209 -- Comp7
1208 -- Comp3

To this:
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1209 -- Comp7

Thanks in advance for your help,
magmike

[Norman Peelman]

Post by magmike
I have created a query that will result in many duplicate listings. The ID number, however is different on each listing. I only want the query to show the record of each company that has the largest id number.
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1211 -- Comp1
1210 -- Comp1
1209 -- Comp7
1208 -- Comp3
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1209 -- Comp7
Thanks in advance for your help,
magmike

SELECT MAX(ID) AS Comp_ID, Company
FROM <table>
GROUP BY Company
ORDER BY Company ASC

[magmike]

Post by magmike
I have created a query that will result in many duplicate listings. The ID number, however is different on each listing. I only want the query to show the record of each company that has the largest id number.
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1211 -- Comp1
1210 -- Comp1
1209 -- Comp7
1208 -- Comp3
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1209 -- Comp7
Thanks in advance for your help,
magmike

Norman,

I am a novice user. Where would I put that code in design view of the query?

[Norman Peelman]

Post by magmike

Post by magmike
I have created a query that will result in many duplicate listings. The ID number, however is different on each listing. I only want the query to show the record of each company that has the largest id number.
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1211 -- Comp1
1210 -- Comp1
1209 -- Comp7
1208 -- Comp3
------------
ID -- Company
1213 -- Comp1
1212 -- Comp3
1209 -- Comp7
Thanks in advance for your help,
magmike

Norman,
I am a novice user. Where would I put that code in design view of the query?

Switch from 'Design' view to 'SQL' view and replace what is there,
obviously substituting your table/field names. Then you can switch back
to 'Design' view and see how it looks there.