August 18, 2009 at 2:16 am
i having below table
SiteId Priority value
1115.00000
1213.00000
1310.00000
211.00000
2225.00000
2316.00000
2290.00000
3111.00000
325.00000
3316.00000
i having above table and want the out put on the base of priority for that site
so out put should be
SiteId Priority value
1115.00000
211.00000
3111.00000
Also i need check that if lowest priority (here 1) is not 0.0
then i need to take subsiquent proirity value (in this case priority will 2 )
ex for site 1 value is 0.0 then value should be imigiate highest priority value
our output will show like below
SiteId Priority value
1213.00000
211.00000
3111.00000
here below is script above table
CREATE TABLE [dbo].[Test](
[SiteID] [int] NULL,
[Priority] [int] NOT NULL,
[Value] [decimal](18, 5) NOT NULL
) ON [PRIMARY]
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(1,1,15.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(1,2,13.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(1,3,10.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(2,1,1.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(2,2,25.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(2,3,16.00)
INSERT INTO [test].[dbo].[Test]([SiteID],[Priority],[Value]) VALUES(2,2,90.00)
August 18, 2009 at 2:32 am
Hi ,
this should do for your first query
with cteSites(SiteID, Priority , Value ,RowN )
as
(
select SiteID ,Priority , Value ,row_number() over ( partition by siteId order by priority asc) from [#test]
)
select SiteID, Priority , Value from
cteSites
where RowN = 1
However , im a little unclear on exactly what you you need from the second.
Please post sample data and expected output
August 18, 2009 at 3:18 am
Hello,
This is my take on it, first add this to your INSERTS:
INSERT INTO [dbo].[Test]([SiteID],[Priority],[Value]) VALUES(3,1,0.00)
INSERT INTO [dbo].[Test]([SiteID],[Priority],[Value]) VALUES(3,2,16.00)
INSERT INTO [dbo].[Test]([SiteID],[Priority],[Value]) VALUES(3,3,25.00)
The above code ensures that you have a Priority = 1 and value = 0 so you can test your code properly.
The code below is what I used to get what I hope you are looking for.
SELECT SITEID, MIN(Priority)Priority, MIN([VALUE])[Value]
FROM (
SELECT * FROM TEST WHERE [VALUE] > 0
) A
GROUP BY SITEID
Hope this helps
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 18, 2009 at 3:30 am
Hi ,
Thanks sir for replay
here is detail about second condition
SiteID Priority value
110.00000
211.00000
3111.00000
consider above result
suppose value siteID 1 having priority 1 but it value is 0.0
then value for siteid 1 should be next hightes priority value
so siteID 1 should have value of priority 2 and not 1
Thus output will be come like below
SiteID Priority value
1 2 13.00000
2 1 1.00000
3 1 11.00000
August 18, 2009 at 3:34 am
Then this should do
with cteSites(SiteID, Priority , Value ,RowN )
as
(
select SiteID ,Priority , Value ,row_number() over ( partition by siteId order by priority asc)
from [#test] where Value >0
)
select SiteID, Priority , Value from
cteSites
where RowN = 1
August 18, 2009 at 4:50 am
I got it
thanks a lot sir!:-)
thank
Vikram
August 18, 2009 at 4:50 am
I got it
thanks a lot sir!:-)
thank
Vikram
August 18, 2009 at 4:50 am
I got it
thanks a lot sir!:-)
thank
Vikram
August 18, 2009 at 4:50 am
I got it
thanks a lot sir!:-)
thank
Vikram
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply