Feching value in the basis of priority

  • 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)

  • 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



    Clear Sky SQL
    My Blog[/url]

  • 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

  • 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

  • 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



    Clear Sky SQL
    My Blog[/url]

  • I got it

    thanks a lot sir!:-)

    thank

    Vikram

  • I got it

    thanks a lot sir!:-)

    thank

    Vikram

  • I got it

    thanks a lot sir!:-)

    thank

    Vikram

  • 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