selecting unique values problem

niths_86 · #1 (**permalink**) 07-31-09, 03:59

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.

r937 · #2 (**permalink**) 07-31-09, 08:21

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

niths_86 · #3 (**permalink**) 08-03-09, 05:47

wow .. thanks a lot ..let me try .

JarlH · #4 (**permalink**) 08-03-09, 11:17

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?

r937 · #5 (**permalink**) 08-03-09, 11:39

your vacation wasn't long enough, you seem to remember some SQL

Internet Services

Web Development

Digital Marketing

Consumer Tech