Hi
I have table like this
State Version
CA 19
CA 8
CA 15
CA 6
MA 12
MA 7
MA 10
MA 5
Like this I have 50 states with different Version No.s (Version No.s are unique)
I want to display output like this(in SQL Server 2000)
State 1st Maximum 2nd Maximum
CA 19 15
MA 12 10
Please advice
Thanks
Here you go..
Code Snippet
Create Table #data (
[State] varchar(3) ,
[Version] int
);
Insert Into #data Values('CA','19');
Insert Into #data Values('CA','8');
Insert Into #data Values('CA','15');
Insert Into #data Values('CA','6');
Insert Into #data Values('MA','12');
Insert Into #data Values('MA','7');
Insert Into #data Values('MA','10');
Insert Into #data Values('MA','5');
Code Snippet
--Only on SQL Server 2005
;With CTE
as
(
Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data
)
Select
[State]
,Max(Case When RowId=1 Then [Version] End) [1st Max]
,Max(Case When RowId=2 Then [Version] End) [2nd Max]
From
CTE
Group By
[State]
Code Snippet
--or
--Only on SQL Server 2005
;With CTE
as
(
Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data
)
Select
[State]
,[1] as [1st Max]
,[2] as [2nd Max]
From
CTE
Pivot
(
Max([Version])
For RowId in ([1],[2])
) as PVT
Code Snippet
--Any SQL Server version
Select
[State]
,Max(Case When RowId=1 Then [Version] End) [1st Max]
,Max(Case When RowId=2 Then [Version] End) [2nd Max]
From
(
Select
[State]
,[Version]
,(Select Count(*) From #data Sub Where Sub.[Version] >= Main.[Version]
And Sub.[State] = Main.[State]) RowId
From
#data Main
) as data
Group By
[State]
|||
wow!!!!!!!
Thanks a lot, Sekar
I got my answer from your nice queries.
Once again Thanks
With Regards
VJ
|||Try:
Code Snippet
createtable dbo.t1 (
[State] char(2)notnull,
Version intnotnull
)
go
insertinto dbo.t1 values('CA', 19)
insertinto dbo.t1 values('CA', 8)
insertinto dbo.t1 values('CA', 15)
insertinto dbo.t1 values('CA', 6)
insertinto dbo.t1 values('MA', 12)
insertinto dbo.t1 values('MA', 7)
insertinto dbo.t1 values('MA', 10)
insertinto dbo.t1 values('MA', 5)
go
select
[State],
max(version)as [1st Maximum],
min(version)as [2nd Maximum]
from
(
select
[state], version
from
dbo.t1 as a
where
version in(
selecttop 2 b.version
from dbo.t1 as b
where b.[state] = a.[state]
orderby b.version desc
)
)as t
groupby
[state]
orderby
[state]
go
-- 2005 - 1
;with cte
as
(
select
[state], version,
row_number()over(partitionby [state] orderby version desc)as rn
from
dbo.t1
)
select
[state],
max(version)as [1st Maximum],
min(version)as [2nd Maximum]
from
cte
where
rn <= 2
groupby
[state]
orderby
[state]
go
-- 2005 - 2
select
a.[state],
max(c.version)as [1st Maximum],
min(c.version)as [2nd Maximum]
from
(
selectdistinct
[state]
from
dbo.t1
)as a
crossapply
(
selecttop 2
b.version
from
dbo.t1 as b
where
b.[state] = a.[state]
orderby
b.version desc
)as c
groupby
a.[state]
orderby
a.[state]
go
droptable dbo.t1
go
AMB
No comments:
Post a Comment