Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-02-23 : 13:58:03

I need a query to get the rownumber 1 for pval value 'H'. irrespective of any order other pval values can be in any other rownumber.


declare @TableVar table ( 
	PID Varchar(5) NOT NULL, 
	Pval Varchar(10) NULL  
)

Insert Into @TableVar (PID,Pval)
(

Select	'100C','H'
UNION ALL
Select	'100C','K'
UNION ALL
Select	'100C','T'
UNION ALL
Select	'100C','L'
UNION ALL
Select	'100C','A'
UNION ALL
Select	'100C','Z'
UNION ALL
Select	'100D','H'
UNION ALL
Select	'100D','Y'

)

select *, ROW_NUMBER() OVER(PARTITION BY pid ORDER BY pval asc) as 'rowno'
from @TableVar

Expected output:

PID	Pval    Rowno
----   ------	-----
100C	H	 1
100C	A	 2
100C	K	 3
100C	L	 4
100C	T	 5
100C	Z	 6
100D	H	 1
100D	A	 2

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-23 : 14:04:17

OVER(PARTITION BY pid ORDER BY case when pval='H' then 1 else 2 end asc)

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-02-23 : 14:30:33

Thansk It works..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources