September 7, 2010 at 6:14 am
empno ename sal deptid
1001 raju 20000.0010
1002 rajesh2000.0020
1003 suresh3000.0030
1004 sravan5000.0020
1005 rakesh25000.0010
1006 anil 45000.0020
1007 babu 2600.0030
1008 scott 6000.0020
1009 prashant8900.0010
1010 chandu1000.0020
1011 kiran 40000.0030
1015 ranjith25000.0010
1012 sat 25000.0020
September 7, 2010 at 6:24 am
Homework? Please show what you've tried and is not working.
Key words you might want to check out are GROUP BY and MAX.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2010 at 6:30 am
I tried with the following query...But
select ename,sal AS maxsal,deptid
from employees
where sal in(select max(sala)
from employees
group by deptid)
order by deptid
But I am getting following wrong out put
rakesh25000.0010
ranjith25000.0010
sat 25000.0020
anil 45000.0020
kiran 40000.0030
here i dont want to get a wrong record like (sat 25000.0020)
September 7, 2010 at 6:34 am
You will get much quicker answers if you post table creation / data insertion scripts as follows:
CREATE TABLE #tblImage (
ImageID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Image] image NOT NULL,
[FileName] varchar(100) NOT NULL,
FileExtension varchar(3) NOT NULL)
INSERT INTO #tblImage ([Image],[FileName],FileExtension)
VALUES ('0x307837383738373','image01','jpg')
INSERT INTO #tblImage ([Image],[FileName],FileExtension)
VALUES ('0x307837383738373','image02','jpg')
INSERT INTO #tblImage ([Image],[FileName],FileExtension)
VALUES ('0x3078333037383337333833373338333733','image03','jpg')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 7, 2010 at 6:36 am
You can also make use of row_number() function as described here
Failing to plan is Planning to fail
September 7, 2010 at 6:53 am
Check it out!!
select ename,Ra.sal,Ra.deptid
from salary_raj as Ra
Inner Join (select max(sal) as S,deptid
from salary_raj
group by deptid) as P on P.S=Ra.sal and Ra.deptid=P.deptid
order by Ra.deptid
Hope this helps.
Cheers
September 7, 2010 at 7:00 am
ranjith.Ileni (9/7/2010)
I tried with the following query...Butselect ename,sal AS maxsal,deptid
from employees
where sal in(select max(sala)
from employees
group by deptid)
order by deptid
But I am getting following wrong out put
rakesh25000.0010
ranjith25000.0010
sat 25000.0020
anil 45000.0020
kiran 40000.0030
here i dont want to get a wrong record like (sat 25000.0020)
Yeah, the problem is you're basically joining on the salary. That's not what you ought to be joining on. Actually, you can do this query without a subselect or a join if you just look at it the right way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2010 at 7:53 am
Check out the syntax for RANK in BOL - it'll do the trick for you here.
September 7, 2010 at 8:30 am
select ename, sal from employees where job_desc = 'dba'
works for me π
---------------------------------------------------------------------
September 7, 2010 at 8:52 am
Madhivanan-208264 (9/7/2010)
You can also make use of row_number() function as described here
Will this work correctly if you have two employees in the same department with the same highest salary?
rakesh 25000.00 10
ranjith 25000.00 10
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 7, 2010 at 10:00 am
george sibbald (9/7/2010)
select ename, sal from employees where job_desc = 'dba'works for me π
Don't forget to enable filestream for the job_responsibilities field if we're including DBAs.
September 7, 2010 at 10:18 pm
Thanks now it isworking
September 7, 2010 at 10:39 pm
Ur solution:
SELECT emp.empno,emp.empname,tmp.Dept ,emp.SAL
FROM empdtls emp
INNER JOIN
(SELECT Max(Sal) Salary, Dept
FROM Empdtls
Group by Dept)tmp
ON tmp.dept = emp.dept
WHERE Salary = emp.Sal
order by tmp.Dept
Create table and insert data using following query:
CREATE TABLE EmpDtls(empno INT, EMPNAME VARCHAR(20),SAL INT, DEPT INT)
INSERT INTO EmpDtls VALUES(1001,'raju',20000,10)
INSERT INTO EmpDtls VALUES(1002,'rajesh',2000,20)
INSERT INTO EmpDtls VALUES(1003,'suresh',3000,30)
INSERT INTO EmpDtls VALUES(1004,'sravan',5000,20)
INSERT INTO EmpDtls VALUES(1005,'rakesh',25000,10)
INSERT INTO EmpDtls VALUES(1006,'anil',45000,20)
INSERT INTO EmpDtls VALUES(1007,'babu',2600,30)
INSERT INTO EmpDtls VALUES(1008,'scott',6000,20)
INSERT INTO EmpDtls VALUES(1009,'prashant',8900,10)
INSERT INTO EmpDtls VALUES(1010,'chandu',1000,20)
INSERT INTO EmpDtls VALUES(1011,'kiran',40000,30)
INSERT INTO EmpDtls VALUES(1015,'ranjith',25000,10)
INSERT INTO EmpDtls VALUES(1012,'sat',25000,20)
September 8, 2010 at 10:11 pm
HI FREINDS,
USING FOLLOWING QRY AS REFERENCE FOR YOUR QRY
SELECT max(item_rate),Grp_ID FROM item_mast
WHERE Grp_ID IN ('001') AND item_rate NOT IN (SELECT max(IM.item_rate) FROM item_mast IM
WHERE Grp_Id=im.Grp_ID GROUP BY Grp_ID) AND ItemCode<>''
GROUP BY Grp_ID ORDER BY Grp_ID
PLEASE CHECK AND REVERT BACK TO ME...
September 9, 2010 at 6:55 am
Derrick Smith (9/7/2010)
george sibbald (9/7/2010)
select ename, sal from employees where job_desc = 'dba'works for me π
Don't forget to enable filestream for the job_responsibilities field if we're including DBAs.
lol and change the salary to decimal(18,2) if you also be payed for the work you have to do for others eg. "my program is slow it must be the Db" crap *lol*
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply