October 11, 2010 at 9:13 pm
I have a table employee with name and age as columns. I need a query with result as EMPNAME and count of employees with ages less than the current employee.
EMPNAME count
abc 5
cde 4
I am trying hard for the query any help would be appreciated.
October 12, 2010 at 12:18 am
tdmech56 (10/11/2010)
I have a table employee with name and age as columns. I need a query with result as EMPNAME and count of employees with ages less than the current employee.EMPNAME count
abc 5
cde 4
I am trying hard for the query any help would be appreciated.
Your posting this in a SQL7/2000 forum so dont really know which version you have. This works in 2000 (but not in SQL7 but a change from table variables to temp tables should make it work in SQL7 as well... cant test that though... sorry)
Something like this should work fairly well even in rather large tables.
/*Test code*/
create table Employee (Name varchar(64), Age int)
insert into Employee (Name, Age)
select 'a', 21 union all select 'b', 31 union all select 'c', 41 union all
select 'd', 51 union all select 'e', 22 union all select 'f', 23 union all
select 'g', 24 union all select 'h', 25 union all select 'i', 42 union all
select 'j', 41 union all select 'k', 43 union all select 'l', 55 union all
select 'm', 60
/*Actual code*/
declare @tbl table (Age int, Nr int)
insert into @tbl (Age, Nr)
select Age, Count(*) from Employee group by Age
declare @tbl2 table (Age int, Nr int primary key (Age))
insert into @tbl2 (Age, Nr)
select Age, IsNull((select Sum(t2.Nr) from @tbl t2 where t2.Age < t.Age), 0)
from @tbl t
group by Age
select e.*, t.Nr
from Employee e
join @tbl2 t on t.Age = e.Age
/*END*/
/*More test code*/
drop table Employee
/T
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply