Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    9

    Unanswered: selecting unique values problem

    Hi ,
    I want to form a select query where i need to display the list of all names and their ids. Here if the name already exists i need to check whether if their date of birth and registration date is different.Only if they are different should i display them.
    Table
    First Name Last name DoB id Regis_Date
    Kevin Patrick 25-05-1987 1 29-07-2009
    Steve Prince 20-1-20009 2 30-07-2009
    Kevin Patrick 25-05-1987 3 29-07-2009
    Steve Prince 23-1-2009 4 29-07-2009

    Output should be
    Name id
    Kevin Patrick 1
    StevePrince 2
    Steve Prince 4 (Since DOB is different)
    please help me in forming the query.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.firstname || t.lastname AS name
         , t.id
      FROM ( SELECT firstname
                  , lastname
                  , dob
                  , regis_date
                  , MIN(id) AS min_id
               FROM daTable
             GROUP
                 BY firstname
                  , lastname
                  , dob
                  , regis_date ) AS d
    INNER
      JOIN daTable AS t
        ON t.firstname  = d.firstname
       AND t.lastname   = d.lastname 
       AND t.dob        = d.dob 
       AND t.regis_date = d.regis_date
       AND t.id         = d.min_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    9
    wow .. thanks a lot ..let me try .

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Just back from vacation I'd simply do:

    Code:
    
    SELECT firstname || ' ' || lastname AS name,
           min(id)
    FROM tablename
    GROUP BY firstname, lastname, DOB, regis_date

    Or am I missing something obvious here?
    Last edited by JarlH; 08-03-09 at 11:20.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your vacation wasn't long enough, you seem to remember some SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •