April 20, 2012 at 12:55 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
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
testCol rowno DUPREQ
a 1 First
a 2 DUP
a 3 DUP
b 1 First
c 1 First
d 1 First
e 1 First
Expected Result
testCol rowno DUPREQ
a 1 First
a 2 DUP
a 3 DUP
b 1 First
c 1 First
d 1 First
e 1 First
April 20, 2012 at 1:01 pm
Given your explanation, do u want to see 2,'a' in your expected reslt?
April 20, 2012 at 1:03 pm
; WITH CTE AS (select ROW_NUMBER () OVER (PARTITION BY testCol ORDER BY (SELECT NULL)) row, * from testPartition)
SELECT testcol, CASE WHEN row = 1 then 'first' ELSE CAST(row AS VARCHAR) + 'dup' END FROM CTE
the cte adds the row numbers restarting with each letter then the second select formats the column as you want.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 20, 2012 at 11:33 pm
I want result which will give the last record flag for a partitioned list
i.e.,for the third row the flag should be LAST
testCol rowno DUPREQ
a 1 First
a 2 DUP
a 3 LAST
b 1 First
c 1 First
d 1 First
e 1 First
April 20, 2012 at 11:45 pm
You can add one more column "rownodesc" similar to "rowno" with ORDER BY option as "testCol DESC"
SELECT*,
CASE
WHEN a.rowno>1 AND a.rownodesc = 1 THEN 'Last'
THEN a.rowno>1 THEN 'DUP'
ELSE 'First' END AS DUPREQ
FROM(
SELECT*,
ROW_NUMBER() OVER (PARTITION BY testCol ORDER BY testCol ) rowno,
ROW_NUMBER() OVER (PARTITION BY testCol ORDER BY testCol DESC) rownodesc
FROMtestPartition
) AS a
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 21, 2012 at 12:01 am
You could add an extra analytic count of the columns and compare that with the row number
with cte as
(
select testcol
,ROW_NUMBER() over (PARTITION by testcol order by testcol asc) as rn
,COUNT(testcol) over (PARTITION by testcol) as cnt
from testpartition
)
select testcol
,case
when rn = 1 then 'first'
when rn = cnt then 'last'
else 'dup'
end as status
from cte
order by testcol asc, rn asc
;
Dave
April 21, 2012 at 12:15 am
Here is what I put together:
CREATE TABLE [dbo].[testPartition](
[testCol] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
);
insert into dbo.testPartition
select 'a'
union all select 'a'
union all select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e';
select * from testPartition;
with BaseData as (
select
ROW_NUMBER() over (PARTITION BY testCol order by (select null)) rownum,
COUNT(*) over (PARTITION BY testCol) cnt,
testCol
from
dbo.testPartition
)
select
testCol,
rownum,
case when rownum = 1 then 'First'
when cnt - rownum > 0 then 'Dup'
else 'Last'
end as DupReq
from
BaseData
;
drop table dbo.testPartition;
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply