Help in query - 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

General SQL Server Forums

New to SQL Server Programming

Help in query

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-21 : 11:18:19

I need a help for the following

I have a table (something similar) like
Id Source Price
1001 X 10
1002 Y 20
1005 X 25
1005 Y 32

I need to select price for those Id's first looking into source for X and if found pick that price field value if not present then look for Y in source and pick that price field value. so the result of the query should be something like

1001 X 10
1002 Y 20
1005 X 25

This should be in single query. any help please.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 11:37:53

Here is one way

declare @t table (id int, source char(1), price int)
insert @t
select 1001, 'X', 10 union all
select 1002, 'Y', 20 union all
select 1005, 'X', 25 union all
select 1005, 'Y', 32

SELECT t.id, 
       t.source, 
       t1.price 
FROM   (SELECT   id, 
                 Min(source) AS source 
        FROM     @t 
        GROUP BY id) t 
       INNER JOIN @t t1 
         ON t.id = t1.id 
            AND t.source = t1.source

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-21 : 13:06:14

Hi vijay,
Thank You very much. I did modified the query to suit my need and it worked for couple of scenarios. I hope it would work for all scenarios. Thank you very much.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-21 : 15:57:22

welcome

Let us know if it doesn't work for all scenarios

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-22 : 04:51:46

try this one too


select id,source,price from 
(select row_number()over(partition by id order by source) as rid ,* from @t)s
where rid = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources