Get Max Number for each department
-
Query for Getting Max number. For Example: I have Department and Employee For DeptId 1: Emp No 12,16,19 and For 2: 18,21,17 For 3: 27,28,16 I have to get Max(EmpNo) For department. If I want to get Max(EmpNo) for Dept(1,2) output will be 19,21. How can I get this using sqlserver2005
-
Query for Getting Max number. For Example: I have Department and Employee For DeptId 1: Emp No 12,16,19 and For 2: 18,21,17 For 3: 27,28,16 I have to get Max(EmpNo) For department. If I want to get Max(EmpNo) for Dept(1,2) output will be 19,21. How can I get this using sqlserver2005
using row_number and partition
SELECT DeptId ,EmpNo
FROM (SELECT
*,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1Never underestimate the power of human stupidity RAH
-
using row_number and partition
SELECT DeptId ,EmpNo
FROM (SELECT
*,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1Never underestimate the power of human stupidity RAH
Thank you so much. Its working fine ...
-
using row_number and partition
SELECT DeptId ,EmpNo
FROM (SELECT
*,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1Never underestimate the power of human stupidity RAH
Why not in this simple way
SELECT DEPTID, MAX(EMPNO) FROM TABLENAME GROUP BY DEPTID
-
using row_number and partition
SELECT DeptId ,EmpNo
FROM (SELECT
*,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1Never underestimate the power of human stupidity RAH
that looks overly complex. wouldn't the following work (I think it does in MySQL):
SELECT DeptId,Max(EmpNo) 'maxEmpNo' FROM table1 GROUP BY DeptId ORDER BY DeptId
:)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Thank you so much. Its working fine ...
See the replies from Luc and Goutam, my answer is NOT the simplest way. Comes from applying the wrong tool for the job.
Never underestimate the power of human stupidity RAH