tricky query is needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

tricky query is needed

Author

Topic

sageora
Starting Member

4 Posts

Posted - 2010-05-20 : 12:49:32

Hi guys!
I'm stuck trying to implement a query that performs the following task:

--Let's say I have a table:
CREATE TABLE SOME_TABLE(
refID int NULL,
value float NULL
)
GO

--with the following populated data:
INSERT INTO SOME_TABLE VALUES (1, 100)
INSERT INTO SOME_TABLE VALUES (1, NULL)
INSERT INTO SOME_TABLE VALUES (1, 50)
INSERT INTO SOME_TABLE VALUES (1, 51)

INSERT INTO SOME_TABLE VALUES (2, 10)
INSERT INTO SOME_TABLE VALUES (2, 20)
INSERT INTO SOME_TABLE VALUES (2, NULL)
INSERT INTO SOME_TABLE VALUES (2, 20)
INSERT INTO SOME_TABLE VALUES (2, NULL)

INSERT INTO SOME_TABLE VALUES (3, NULL)
INSERT INTO SOME_TABLE VALUES (3, NULL)
INSERT INTO SOME_TABLE VALUES (3, NULL)

GO

For each particular refID I need to return one record when there is no other then NULL values,
and also return all not null values and in this case filter out all the NULL cases.
The result should look like this:
refID Value
1 100
1 50
1 51
2 10
2 20
2 20
3 NULL

Any help is appreciated.
Thanx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 13:21:15

Try this

select * from 
(
select row_number() over(partition by refID order by [value] desc) as seq ,* from SOME_TABLE
) t
where 
(
case when t.seq = 1 and [value] is NULL then 1 
else case when [value] is not null then 1 else 0 end
end ) = 1

sageora
Starting Member

4 Posts

Posted - 2010-05-20 : 13:39:38

Thank you very much!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-20 : 14:15:18

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources