How to find Distinct record using GROUP BY?

How to find distinct records using group by? Then what is the difference between group by and Distinct?

Taking a scenario where we are having a table with duplicate records like the one mentioned below:

select first_id,name from vw_parent_tab

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

(4 row(s) affected)

Now if we want to find distinct record we can easily do it with the help of distinct clause

select distinct first_id,name from vw_parent_tab

first_id    name

----------- ------------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

But the question is how to find the same distinct result using GROUP BY Clause; Answer is simple add a group by clause and put all the field you want to display using select query with the Group By.

select first_id,name from vw_parent_tab group by first_id,name

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

If you are using a group by without any aggregate function then internally it will be treated as Distinct so in this case there is no difference between group by and Distinct...
But when you are provided with Distinct Clause better to use it for finding your unique records because objective of group by is to achieve aggregation not distinct.

Use of Group by:

select first_id,name,count(*) as 'duplicate_count' from vw_parent_tab group by first_id,name

first_id    name                       duplicate_count

----------- -------------------------- ---------------

3           pratik                     2

1           sneha                      2

(2 row(s) affected)

A hammer can work to drive in a screw sometimes, but if you have got a screwdriver handy, why bother?

Conclusion:  Use Distinct when you want distinct records whereas use group by when you want your records to be aggregated.

Post Reference: Vikram Aristocratic Elfin Share