QUERY to return only 1 entry per duplicate

[StellaIndigo]

I have a table,

order_number (number)
status_code (number)
date_of_status (date/time)

An order_number can have several entries for each time the status code is updated/progressed so, for example,

000001,100,10/11/2003 11:00:00
000001,101,10/11/2003 11:20:00
000001,102,12/11/2003 17:45:00
000222,100,11/11/2003 09:14:00
000222,104,12/11/2003 11:10:00
000222,109,17/11/2003 12:01:00

I want to query the table but one return the very latest entry for each order number, so the query would return,

000001,102,12/11/2003 17:45:00
000222,109,17/11/2003 12:01:00

I've indexed the table on order_number + status_code + date_of_status <--(decending) but it don't work.

Can any one point me in the right direction.

Ta.



There are 10 types of people in the world. Those that understand binary and those that don't.

 

[MakeItSo]

create a query in query designer, add all fields you need,
then click on the sum symbol (grouping) and in the grouping selector field select &quot;max&quot; for your date_of_status field.

That should do it. ;-)

Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[StellaIndigo]

I see, you mean a cross tab query, order number in the rows, other data in the column headings and max the status date coumn...

...ok i'll give it a go.

many thanks for the reply.

Stella

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[StellaIndigo]

Actually I did a normal query and clicked Totals on. Then did a max status_code AND date_of_status. Without both it returns each status code for each order which I didn't want. Since the status_code increases as the order moves this doesn't present a problem.

There are 10 types of people in the world. Those that understand binary and those that don't.

 

[TommyP]

You could also create a standard &quot;Duplicate&quot; query and un-check the &quot;Show&quot; checkbox in the &quot;Count&quot; column.

 

[tracy1234]

See, the problem when using a max like max(status_date) is that you have to use a group by. So if you have both a unique status_code and a status date then you run into trouble. 'Group by' is grouping by unique values.

If you absolutely need to return both status_code and status_date then you could try using last instead of group by in your query for the status_code column. I have sometimes used this workaround to cheat the group by.

I'm sure the pros will laugh but I figure if it works, it works.

-Tracy