unsolved database Query

[nohal.surya]

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

[Mikav6]

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.

[abhay yadav]

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;

[nohal.surya]

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);

[adam_k]

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.