remove imperfect duplicates based on multiple criteria

[goldfrapp01]

Hi everyone,
struggling with writing a query to retrieve valid records from another query, let say "Query1"
This is what I would like my new Query2 to do:
For records with the same Client_id (that appear 1 or more times), and if their order_date and order_type are the same, give me the records whose client_type is NOT "e.client".
For some reason sometimes the same order enters the table twice, and the e.client one is always invalid. Of course I;d like to keep those records where order_date an order_type are different, because they represent distinct orders, does not matter if it is "e.client" or "client".
I hope it is clear, I am struggling with a number of WHERE and HAVING statements that does not give me the intended result.
Any help will be appreciated! Thanks everyone,

Client_id	Client_Name	order_date	order_type	cilent_type	Intended result
1	Alpha	18/1/2012	Web	client	Keep
2	Bravo	19/2/2012	Phone	e.client	Keep
3	Charlie	25/2/2012	Web	client	Keep
3	Charlie	15/3/2012	Web	e.client	Keep
4	Delta	17/4/2012	Web	client	Keep
4	Delta	17/4/2012	Web	e.client	Remove
5	Echo	17/4/2012	Web	e.client	Keep
5	Echo	17/4/2012	Phone	e.client	Keep
6	Golf	25/7/2012	Web	client	Keep
6	Golf	25/7/2012	Web	e.client	Remove
6	Golf	30/7/2012	Web	e.client	Keep

1 and 2 - keep because they exist once.
3. keep due to different order dates
4. Remove the e.client because order_date and order_type are the same
5. keep due to different order types
6. Remove the e.client entry that has a duplicated order_date and order_time.

[rpeare]

Make your query an AGGREGATE query (look for the SIGMA, looks like a capital E on your toolbar)

In the TOTAL row of the query (right below the field and table) have GROUP BY for everything except CLIENT_TYPE, which should be MIN

[goldfrapp01]

Make your query an AGGREGATE query (look for the SIGMA, looks like a capital E on your toolbar)

In the TOTAL row of the query (right below the field and table) have GROUP BY for everything except CLIENT_TYPE, which should be MIN

Thanks, this is very good, however sometimes client_type starts with letters after E alphabetically so the MIN condition won't work in this case. Can I use an expression to filter "e.client" explicitly? Or is this not allowed in an aggregate function?

Thanks,

[rpeare]

your problem is that you do not want to filter e.client out completely, only in the absence of all other client_types. You haven't really told us all the rules what happens if you have something like

4	Delta	17/4/2012	Web	client	Keep
4	Delta	17/4/2012	Web	e.client	Remove
4	Delta	17/4/2012	Web	e-type3	?

Can you have a situation like this? where a client has 3 types? if so what are your rules does it get the lowest alphabetically, the highest alphabetically?

If you are storing your client type on a separate table you can also add a SORT field so that you can prioritize your client types when you run your query barring that you could likely do something with a min and max value for instance evaluate the min and max client_type if they are equal, just use the min or max client_type. If they are different choose the one that's not e.client