April 20, 2012 at 11:40 pm
step 1: Please create a table below
CREATE TABLE [dbo].[testPartition](
[testCol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
stpe 2:
Please insert the test data as below
insert into testPartition
select 'a'
union select 'a'
union select 'a'
union select 'b'
union select 'c'
union select 'd'
union select 'e'
step 3:
A Partiton by query on the column in the table using below query will give a result shown below, I require a query which will give the first and last fields for a partitioned list, so the expected result is shown in the 2nd table below
thrird row in the result should be LAST as this is the last row for partitioned list with partition value a 'a'.
select *,case when a.rowno>1 then 'DUP' else 'First' end as DUPREQ from
(select *,row_number() over (partition by testCol order by testCol) rowno
from testPartition ) as a
testColrownoDUPREQ
a1First
a2DUP
a3DUP
b1First
c1First
d1First
e1First
Expected Result
testColrownoDUPREQ
a1First
a2DUP
a3LAST
b1First
c1First
d1First
e1First
April 20, 2012 at 11:48 pm
Please don't create duplicate threads for the same issue. This will fragment the replies.
Original Thread: http://www.sqlservercentral.com/Forums/Topic1287441-392-1.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply