SQL Server: Function that returns value if first or last row in result set? -

i'm wondering if there function in sql server can this:

select      itemid,      itemtitle,      isfirstrow(itemid) isfirst,      islastrow(itemid) islast mytable  order itemid 

example desired results:

itemid  itemtitle     isfirst  islast ------  ------------  -------  ------      1  item       true     false      2  world     false    false      3  code          false    false      4      false    true 

the thing want first , last row values representative of specific ordering of table, not order in results returned. in case, itemid value key.

select      itemid,      itemtitle,      isfirstrow(itemid ascending) isfirst,      islastrow(itemid ascending) islast  mytable  order itemtitle  itemid  itemtitle     isfirst  islast ------  ------------  -------  ------      1  item       true     false      3  code          false    false      4      false    true      2  world     false    false 

or example:

select      itemid,      itemtitle,      isfirstrow(itemtitle descending) isfirst,     islastrow(itemtitle descending) islast mytable  itemid > 1 order itemid  itemid  itemtitle     isfirst  islast ------  ------------  -------  -----------      2  world     true     false      3  code          false    false      4      false    false 

the use case returning result set, or single record result set, front end application, allowing front application know whether it's first or last record in specific ordering without having make separate query , "count of records" or "max/min" strategy (which wouldn't work on text field).

i'm imagining subquery might able not sure how syntax look.

you can using case , row_number

create table #temp (   itemid int,   itemtitle varchar(10), )  insert #temp values (1, 'an item') insert #temp values (3, 'code') insert #temp values (4, 'something') insert #temp values (2, 'the world')   select    itemid,   itemtitle,   case when row_number() on (order itemid asc) = 1 'true' else 'false' end isfirst,   case when row_number() on (order itemid desc) = 1 'true' else 'false' end islast #temp order itemtitle