Post reply

Re-Post and Apologies

  • October 16, 2003 at 3:06 am

    #82804

    Hello, I posted this yesterday however I forgot I did not show the entire results I wanted.

    I have a database table with the following

    ID Surname Forename

    1 Smith Brian

    2 Smith John

    3 Jones Brian

    4 Smith Brian

    5 Doyle Susan

    6 Jones Brian

    I need SQL to remove the duplicates based on the Forename / Surname to get

    1 Smith Brian

    2 Smith John

    3 Jones Brian

    5 Doyle Susam

    I have tried using thee Min command with no great effect.

    Can anyone help ?

    Thanks

    J

  • October 16, 2003 at 3:21 am

    #478405

    hi,

    i think this will do the job :-

    create table cust_names

    (cust_id int, surname varchar(20), forename varchar(20))

    insert into cust_names values(1,"Smith", "Brian")

    insert into cust_names values (2, "Smith", "John")

    insert into cust_names values (3, "Jones", "Brian")

    insert into cust_names values (4, "Smith", "Brian")

    insert into cust_names values (5, "Doyle", "Susan")

    insert into cust_names values (6, "Jones", "Brian")

    select cn1.cust_id, cn1.surname, cn1.forename

    from cust_names cn1

    where cn1.cust_id = (select min(cn2.cust_id) from cust_names cn2

    where cn2.forename = cn1.forename

    and cn2.surname = cn1.surname)

    order by cust_id

    Paul

  • October 16, 2003 at 4:04 am

    #478407

    Dear Paul,

    Thank you, that works brilliantly, my solution took about 100X the time

    J

    Edited by - JulieW on 10/16/2003 04:07:17 AM

  • October 16, 2003 at 4:49 am

    #478412

    hi, glad to be of help, I use things like this quite frequently, well worth remembering.

    Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply