Sql Distinct Query

Question

I have certain fields like

Id
ModuleId
ModuleName
TotalAmount
Category

Here in my case the value of only Id and ModuleId used to change.
But I want to get whole record from this table with unique or distinct ModuleId and that too(?) the First Row which is occuring.

I tried distinct but it fails as Id's value also changes. Very much appreciated if answered because with out this i cant go forward with my query.

Answer 1

Should work on most DBMS

select Id, ModuleId, ModuleName, TotalAmount, Category
from tbl
where id in 
(
select min(id)
from tbl
group by moduleid
)

Answer 2

You can use aggregate functions like sum, avg, max, min.

select id, ModuleId, max(modulename), avg(TotalAmount), max(Category)
from table
group by id, ModuleId