problems - 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 2005 Forums

Transact-SQL (2005)

problems

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-12 : 06:50:39

i have tables TABLEA (i have more columns)

# ColumnA CoulmnB ColumnC ......
1 1 2 P
2 1 2 O
3 2 20 U
4 2 5 F
5 4 3 O

if Row of ColumnA And ColumnB equal so i want just the row of Columns p i mean:

# ColumnA CoulmnB ColumnC ......
1 1 2 P
3 2 20 U
4 2 5 F
5 4 3 O

how i can do it?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-12 : 07:07:15

[code]
declare @TABLEA table
(
[#] int,
ColumnA int,
ColumnB int,
ColumnC char(1)
)
insert into @TABLEA
select 1, 1, 2, 'P' union all
select 2, 1, 2, 'O' union all
select 3, 2, 20, 'U' union all
select 4, 2, 5, 'F' union all
select 5, 4, 3, 'O'

select [#], ColumnA, ColumnB, ColumnC
from (
select [#], ColumnA, ColumnB, ColumnC,
row_no = row_number() over (partition by ColumnA, ColumnB order by ColumnC DESC)
from @TABLEA
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-12 : 10:02:37

quote:
Originally posted by inbs

i have tables TABLEA (i have more columns)

# ColumnA CoulmnB ColumnC ......
1 1 2 P
2 1 2 O
3 2 20 U
4 2 5 F
5 4 3 O

if Row of ColumnA And ColumnB equal so i want just the row of Columns p i mean:

# ColumnA CoulmnB ColumnC ......
1 1 2 P
3 2 20 U
4 2 5 F
5 4 3 O

how i can do it?

thanks

will the columnC values be always P,U,F,O...what do you want if you've more than 1 occurance of cola,colb but with different ColC values (may be U & F)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources