CODEWITHSUNDEEP
sqloracle-databasedatejoingroup-by
Fərid Qənbərli
Fərid Qənbərli

Reputation: 118

Checking only for duplicate rows

I want to check if all the years in the category are the same and return if so, so I want to return only 4 and 5. I could get this table, but no further.

SELECT 
    st.category,  
    EXTRACT ( YEAR FROM hiredate) as YEAR 
FROM NIKOVITS.EMP e, 
NIKOVITS.SAL_CAT st 
WHERE e.sal > st.lowest_sal AND e.sal < st.highest_sal

Result of the query

Upvotes: 2

Views: 43

Answers (1)

GMB
GMB

Reputation: 222432

You can use aggregation:

select st.category, min(extract(year from e.hiredate)) yr
from nikovits.emp e
inner join nikovits.sal_cat st 
    on  e.sal > st.lowest_sal 
    and e.sal < st.highest_sal
group by st.category
having min(extract(year from e.hiredate)) = max(extract(year from e.hiredate))

Note that this query uses standard join syntax with the on keyword) rather than implicit join (with a comma in the from clause). This old syntax from decades ago should not be used in new code.

Also, all columns in the query should be qualified with the table they belong to, to avoid ambiguity; I made the assumption that hiredate belongs to table emp.

Upvotes: 1

Related Questions