i have a workers table which contains 4 records such as (id,name,category, status) ...
category like : a,b,c
for example :
id - name - category - status
1 - Jone - A - free
2 - Tom - B - busy
3 - Adm - c - free
4 - Raul - B - free
5 - Sami -A - busy
i want to write query to obtain 3 workers that their status = free and belong to each category (a,b and c )
such as here :
1 - Jone - A - free
3 - Adm - c - free
4 - Raul - B - free
can you help me ?Hope this work for you.
Good luck!
SELECT id,
name,
category
FROM WORKERS
WHERE status = 'free'
|||
Use this query...
select Workers.* From Workers Join
(
Select Category,Max(main.id) id From Workers main Where 3 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From Workers main Where 2 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From Workers main Where 1 =
(Select Count(*) From Workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
) as Data On Data.id = Workers.Id
Order By 3
It will exactly obtain 3 workers that their status = free and belong to each category
|||-- --
-- Kanjo:
--
-- The things that I understand from your request are:
-- 1. You do want to filter out any status other
-- than the 'free' status.
-- 2. You want to select the worker information
-- for 'free' workers -- especially the category
-- of the worker.
--
-- The things that I am not sure of are:
-- 1. Do you want to filter out any worker that is
-- not in catories 'A', 'B' or 'C'?
-- 2. Do you want all free users in any of the
-- categories or
-- 3. Do you only want the 'Top' free worker of
-- each of the categories?
-- 4. Are you running SQL Server 2000 or 2005?
-- (In this case it might not matter.)
--
-- Each of these isues has an impact on the way that
-- the query needs to be written.
-- --
set nocount on
declare @.workers table
( id integer not null,
name varchar (20) not null,
category char (1) not null,
status varchar (10) not null
)
insert into @.workers values (1, 'Jone', 'A', 'free')
insert into @.workers values (2, 'Tom' , 'B', 'busy')
insert into @.workers values (3, 'Adm', 'C', 'free')
insert into @.workers values (4, 'Raul', 'B', 'free')
insert into @.workers values (5, 'Sami', 'A', 'busy')
insert into @.workers values (6, 'Roni', 'A', 'free')
insert into @.workers values (7, 'Suzy', 'D', 'free')
--select * from @.workers
-- --
-- If there is no need to filter based on category
-- Vincent's query will work just fine:
-- --
select id,
name,
category
from @.workers
where status = 'free'
-- and category in ('A','B','C')
-- Sample Output:
-- id name category
-- -- -- --
-- 1 Jone A
-- 3 Adm C
-- 4 Raul B
-- 6 Roni A
-- 7 Suzy D
-- --
-- However, if you do need to filter based on the
-- category then the where clause will need to be
-- modified to reflect that need
-- --
select id,
name,
category,
status
from @.workers
where status = 'free'
and category in ('A','B','C')
-- - Sample Output --
-- id name category status
-- -- -- -- -
-- 1 Jone A free
-- 3 Adm C free
-- 4 Raul B free
-- 6 Roni A free
-- --
-- There are other ways of obtaing the "top" list if
-- that is the requirement; this is just one of the
-- ways.
-- --
select a.id,
a.name,
a.category,
a.status
from @.workers a
inner join
( select category,
min (id) as min_id
from @.workers
where category in ('A','B','C')
group by category
) b
on a.id = b.min_id
-- -- Sample Output:
-- id name category status
-- -- -- -- -
-- 1 Jone A free
-- 2 Tom B busy
-- 3 Adm C free
-- --
-- Mani:
--
-- HELP! I am not getting the expected results and
-- I am still trying to figure out why. Can you spot
-- where I went wrong?
-- --
select workers.* From @.workers workers Join
(
Select Category,Max(main.id) id From @.workers main Where 3 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 2 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 1 =
(Select Count(*) From @.workers Sub Where Main.id < Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And Status='Free'
Group BY
Category
) as Data On Data.id = workers.Id
Order By 3
-- Unexpected Output: -
-- id name category status
-- -- -- -- -
-- 1 Jone A free
Fixed... ThanQ!
select workers.* From @.workers workers Join
(
Select Category,Max(main.id) id From @.workers main Where 3 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 2 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
Union All
Select Category,Max(main.id) From @.workers main Where 1 =
(Select Count(*) From @.workers Sub Where Main.id <= Sub.ID And Main.Category = Sub.Category And Main.Status=Sub.Status) And main.Status='Free'
Group BY
Category
) as Data On Data.id = workers.Id
Order By 3
