Re: How to: Remove duplicate TITLES but keep a COUNTRY code

From: Joel (smilingface65_at_hotmail.com)
Date: 06/15/04 

Date: Tue, 15 Jun 2004 17:37:08 -0400

Take advantage of the fact that USA is alphabetically the MAX value:

Select TITLE, Max(Origin) From Table Group by Title

If there is no USA in one of the choices, you get the highest one available
alphabetically.

"leetrainer" <leetrainer@discussions.microsoft.com> wrote in message
news:FC3B6A2A-A98B-4978-8BA9-553B8A97A00A@microsoft.com...
> Hi all
>
> I'm trying to find out how to Retrieve Data from a Single Table and was
wondering if someone could help with the following as I cant seem to find
the answer to what is probably an easy question for the experts. I'm a
Visual FoxPro 6 user and use the following code to almost accomplish what
I'm after...
>
> SELECT * FROM mytabletemp INTO TABLE LISTINGS GROUP BY TITLE
>
> This works perfectly and removes any duplicate entries with the same TITLE
>
> My problem (and yes it's been a problem!) is that I have four possible
country of ORIGIN codes such EEC - USA - CAN - DOM
>
> When you issue the above command it does remove duplicates but keeps a
country of ORIGIN such as EEC.
>
> What I'm trying to do is search out any duplicate TITLES as shown below
but keep the USA ones
> Example:
>
> ADAMS, BRYAN - BEST OF - EEC
> ADAMS, BRYAN - BEST OF - USA
> ADAMS, BRYAN - BEST OF - CAN
> ADAMS, BRYAN - BEST OF - DOM
>
> I did try:
> SELECT * FROM mytabletemp INTO TABLE LISTINGS GROUP BY TITLE WHERE
ORIGIN="USA"
>
> but this removes all other ORIGIN such as CAN EEC etc
>
> So, how do I remove three of the above just keeping the USA title?
>
> I would be more than grateful if anyone can suggest the answer
> Kindest regards
> Lee

Relevant Pages

  • Re: How to: Remove duplicate TITLES but keep a COUNTRY code
    ... Please provide the table schema and sample data. ... > This works perfectly and removes any duplicate entries with the same TITLE ... country of ORIGIN codes such EEC - USA - CAN - DOM ...
    (microsoft.public.sqlserver.programming)
  • Re: Belgium, man!
    ... different country. ... Let's call it Belgium for the sake of argument, ... When people come here they need to be EEC, ... presumably the B & B type housing. ...
    (uk.media.radio.archers)
  • Re: How to: Remove duplicate TITLES but keep a COUNTRY code
    ... Thanks for the quick response. ... Any other suggestion? ... >> This works perfectly and removes any duplicate entries with the same TITLE ... > country of ORIGIN such as EEC. ...
    (microsoft.public.sqlserver.programming)
  • How to: Remove duplicate TITLES but keep a COUNTRY code
    ... This works perfectly and removes any duplicate entries with the same TITLE ... ADAMS, BRYAN - BEST OF - EEC ... SELECT * FROM mytabletemp INTO TABLE LISTINGS GROUP BY TITLE WHERE ORIGIN="USA" ...
    (microsoft.public.sqlserver.programming)
  • How to delete duplicates based on data in 2 columns?
    ... I have a 2 column spreadsheet. ... numbers and Column B may or may not contain a duplicate country of origin's. ... In the example I'd want to get rid of the first line and the last line. ...
    (microsoft.public.excel.misc)