SQL multiple distinct

pts.
Tags:
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Alex8809
    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)
    0 pointsBadges:
    report
  • Stevewaltz
    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.
    0 pointsBadges:
    report
  • Harinck
    Most times you can solve this problems with 'GROUP BY'. For field like 'date' you can use aggregate functions like 'MIN' or 'MAX'.
    0 pointsBadges:
    report
  • Jiehong
    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!
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following