can any one explain how to get distinct records from a table.?

Question

How do I get distinct records from a table?

Someone suggested this query to me:

select * from emp a 
where rowid = (select max(rowid) from emp b 
               where a.empno=b.empno);

Is rowid is unique for each row inserted? What is the use using max(rowid)?

Why can't I use the below query?

select distinct sal, distinct empno from emp;

Answer 1

1. rowid is unique for each row and the proposed request will get the last record inserted for each empno

2. Don't repeat DISTINCT, the following SQL Request will return all distinct couple of sal, empno

   select distinct sal, empno from emp;
   

Answer 2

You can use group by for getting distinct records.

select sal, empno from emp group by Sal,empno;

Answer 3

delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);

Answer 4

Select eid, count() from employee group by eid having count()=1