Filter in a query

Question

Filter in a query

mail-1077909

Newbie

Points: 3
More actions
April 28, 2009 at 2:47 am

#204057

Hi Folks
I'm struggling with a query & hoping that I could get some help...Here's the situation. I have a query that returns a calculated cost (derived column) per employee:
NAMECOSTTASK1TASK2
------ ------- ------- -------
JACK348042
BOB232575
PETER565083
JACK5110056
JACK221034
WILLIAM311517
What I would like to do is filter this data, by unique names and lowest cost, such that I get the following result:
NAMECOSTTASK1TASK2
------ ------- ------- -------
BOB232575
PETER565083
JACK221034
WILLIAM311517
I can't use aggregation since for TASK1 & TASK2 I would get different results.
Any ideas/suggestions? Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

here's an example which gets the lowest task for each row; you simply compare the column values in a case statement. that will get you started on which tasks to select.

select

name,

case

when task1 < task2

then task1

else task2

end as mintask

from (SELECT 'JACK' AS NAME,34 AS COST, 80 AS TASK1, 42 AS TASK2

UNION ALL

SELECT 'BOB', 23, 25, 75 UNION ALL

SELECT 'PETER', 56, 50, 83 UNION ALL

SELECT 'JACK', 51, 100, 56 UNION ALL

SELECT 'JACK', 22, 10, 34 UNION ALL

SELECT 'WILLIAM', 31, 15, 17) MYSUBQUERY

--Results:

name mintask

JACK 42

BOB 25

PETER 50

JACK 56

JACK 10

WILLIAM 15

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mail-1077909 Newbie Points: 3 More actions · Answer 2

Thanks for the prompt response Lowell, but I need to filter on rows not columns...so in my example I'm looking for unique employee names with all 3 columns, and where there are multiple rows for an employeee I need the row with the lowest cost.

Thanks again!

Lowell SSC Guru Points: 323546 More actions · Answer 3

sorry i mis understood, i thought you wanted each of the lower TASKs..

in that case, i'd use the row_number() feature and an outer select:

SELECT * FROM (

select row_number()over (PARTITION BY NAME ORDER BY cost) AS RW,

MYSUBQUERY.*

from (SELECT 'JACK' AS NAME,34 AS COST, 80 AS TASK1, 42 AS TASK2

UNION ALL

SELECT 'BOB', 23, 25, 75 UNION ALL

SELECT 'PETER', 56, 50, 83 UNION ALL

SELECT 'JACK', 51, 100, 56 UNION ALL

SELECT 'JACK', 22, 10, 34 UNION ALL

SELECT 'WILLIAM', 31, 15, 17) MYSUBQUERY

)MYOTHERALIAS WHERE RW = 1

results:

RW NAME COST TASK1 TASK2

-------------------- ------- ----------- ----------- -----------

1 BOB 23 25 75

1 JACK 22 10 34

1 PETER 56 50 83

1 WILLIAM 31 15 17

(4 row(s) affected)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mail-1077909 Newbie Points: 3 More actions · Answer 4

Lowell,

Thanks a million! I will give it a try right away. 😛