SQL multiple distinct
I have a program in SQL plus that is currently duplicating. I beleive i need to add a distinct. I currently have a distinct at the beginning of my select, but i need to another distinct somewhere in the middle. how do i do multiple distincts. I.E;
select distinct 'ver12345', a.ssn_id, b.ssn_id, (I need a distinct at this point,)a.last_name.
Software/Hardware used:
Software/Hardware used:
ASKED:
August 11, 2005 8:12 AM
UPDATED:
August 17, 2005 6:44 PM
Answer Wiki:
The distinct (or unique) specification refers to the entire row being returned. If there are any differences in ssn or last name, you will get all rows which are not exactly the same. It sounds like you only want 1 occurrence of each ssn, but if there are 2 rows with the same ssn_id but different last names, which last name do you want? I find it less confusing to use the group by function in some cases. For this inquiry, I might select a.ssn_id, count (a.ssn), min(last_name), max(last_name) and group by a.ssn_id. I didn't include b.ssn_id because I'm guessing you are joining a and b when a.ssn_id = b.ssn_id. There's no point in repeating the ssn_id, but you may be restricting listing of rows from a to those cases where there is a row in b with the same ssn_id. Or you may have left out additional columns you need from b to simplify the question. If so, that could affect the way you write the query.
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you for your responce.
The sittuation is that I am bringing back personnel information.
For example;
employee_ssn employee_id_ssn employee_lastname employee_firstname employee_coveragetype employee_coveragestartdate.
Then under the employee info i bring back the dependent informaton.
example;
employee_ssn dependent_id_ssn dependent_lastname
dependent_firstname dependent_coveragetype
dependent_coveragestartdate.
My current results are coming back duplicated. I get 1 good employee and duplicated dependents, since dependents tables has 2 set of coverage type and coverage dates (uhcall 1/1/04) and (ubasic 12/31/03). I need to bring back the max date. so (uhcall 1/1/04)
If I understand what you are attempting, analytic functions may be helpful. The last_value function would allow you to select the row with the latest date if you order the results from the dependent table by date. Prior to the introduction of analytic functions in Oracle 8i, the only way I knew to do what you are attempting in SQL was to use a subquery. One part of the query would find the latest date for a particular ssn_id and another would would find the data associated with the rowid or some unique key value for that row.
Most times you can solve this problems with ‘GROUP BY’. For field like ‘date’ you can use aggregate functions like ‘MIN’ or ‘MAX’.
You can use a SQL like following to achieve what u required:
SELECT e.*, d.*
FROM employee e, dependent d,
(select d1.employee_ssn, d1.dependent_id_ssn, max(d1.coveragestartdate) startdate from dependent d1
GROUP BY d.employee_ssn, d.dependent_id_ssn) dd
WHERE e.employee_ssn = d.employee_ssn
and d.employee_ssn = dd.employee_ssn
AND d.dependent_id_ssn = dd.dependent_id_ssn
AND d.coveragestartdate = ddstartdate
Good luck!