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

Recommended Answers

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 …
Jump to Post

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 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.