February 23, 2006 at 1:26 pm
I have the following table:Id line_id status category10 1 4 111 1 4 212 1 1 1What I want is to say if there are rows for the same line_id and same category then select the one with status 4. Can someone help, please?
February 23, 2006 at 2:22 pm
step 1) add an identity to the table. Life will be simpler in soo may ways
(tongue in cheek column name)
id_id line_id status category10
11 4 111
21 4 212
31 1 1
step 2) I'm not really understanding what you want to do.
are you trying to pull the data from ID, and if there
are multiple line_id, category10 pick the one with status 4?
(implying there are move columns, which you want the data from)
February 23, 2006 at 2:36 pm
Probably isn't close to what you want but...
create table id (id_id int identity, line_id int, status int, category10 int, other varchar(20))
select * from ID
id_id line_id status category10 other
111111a
214111b
314212c
4111d
SELECT * FROM id WHERE status = 4
UNION
SELECT * FROM id
WHERE cast(line_id as varchar(4)) + cast(category10 as varchar(10)) IN
(select cast(line_id as varchar(4)) + cast(category10 as varchar(10))
FROM ID
GROUP BY cast(line_id as varchar(4)) + cast(category10 as varchar(10))
HAVING max(status) = 1)
Ugly but it works... (and will keep bothering me)
cheers
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply