Q-->
i have a employee table which cantain following field (eid,enm,salry,did)
and i need a list which has employeies complete information whose get minimum saler in each department

*** only single list cantain all employee info whose get minimum salry

## All 4 Replies

Generally there are (at least) two ways to do this but not all databases support both. If database supports nested queries, you can use the following syntax:

``SELECT EMP.* FROM EMPLOYEE EMP JOIN (SELECT DID AS DID2,MIN(SALRY) AS SAL2 FROM EMPLOYEE GROUP BY DID) ON EMP.SALRY=SAL2 AND EMP.DID=DID2``

that works for example on Firebird database but not on MS SQL Server. On SQL databases that do not support nested queries, you can always build a temp table and use the following syntax:

``````CREATE TABLE TEMPEMP(DID INTEGER,SALRY NUMERIC(15,2));
INSERT INTO TEMPEMP(DID,SALRY) SELECT DID,MIN(SALRY) FROM EMPLOYEE GROUP BY DID;
SELECT EMPLOYEE.* FROM EMPLOYEE, TEMPEMP WHERE EMPLOYEE.DID=TEMPEMP.DID AND EMPLOYEE.SALRY=TEMPEMP.SALRY;
DROP TABLE TEMPEMP;``````

So 1st create a temp table with minimum salary per departement and then use that to build the final query.

hiii frnds
create table employee (eid (number(10)),enm (varchar2(10),salry number(5),did varchar2(10));
insert into table employee values(&eid,&enm,&salry,&did);
select eid,enm,salry,did,min(salry) from employee group by did;

hi frnd it is not right ans
m solved nearest ans of this but not perfect ans of the query
m waiting for replay

SELECT eid, enm, sal, did
FROM employee
WHERE sal in (select min(sal) from employee group by did);

``````select emp.* from employee emp
inner join
(select did, min(sal) as 'min_sal' from employee
group by did) emp2
on emp.did = emp2.did
and emp.sal = emp2.min_sal``````

@Mikav6: Of course MS SQL supports nested queries, you just have to assign an alias to the query, before using it, as in my query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.