Select Query get records by Type

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)

Select Query get records by Type

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-12 : 05:52:49

Is it possible to validate type field inside the select.

select taskid, amount, type from tableTasks

For each taskid there can be 3 types of records available in the table: FINAL or PTE or FTE

if type "FIN" is available for taskid (get "FIN") otherwise get "PTE" otherwise "FTE" type.

Thank you very much for the help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 06:08:29

just try this

select case when [type] = 'FIN' then taskid
when [type] ='PTE' then taskid
when [type] ='fte' then taskid end as id from tableTasks

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-12 : 06:11:35

[code]
declare @table table (id int,string varchar(777))
insert into @table
select 1,'FINAL' union all
select 1,'PTE' union all
select 1,'FTE' union all
select 2,'PTE' union all
select 2,'FTE' union all
select 3,'FTE'

select t.id,t.string from
(select row_number() over(partition by id order by case when string = 'final' then 0 when string = 'PTE' then 1 when string = 'fte' then 2 end) as rowno,* from @table ) t
where t.rowno = 1

[/code]

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 06:18:45

hai jai,
add this statement union all
select 3,'PTE' and check the output once

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-12 : 06:21:17

quote:
Originally posted by bklr

hai jai,
add this statement union all
select 3,'PTE' and check the output once

Its working bklr my query is giving correct o/p when i inclued what u said to include

Jai Krishna

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-12 : 06:34:01

Jai,

Thank you very much, it is a good example.

quote:
Originally posted by Jai Krishna

quote:
Originally posted by bklr

hai jai,
add this statement union all
select 3,'PTE' and check the output once

Its working bklr my query is giving correct o/p when i inclued what u said to include

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

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

Welcome

Jai Krishna

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-02-14 : 11:58:50

Hello Jai,

You gave a very good answer. but i am having a problem.
There is also another type of strings available. but i want to ignore those two strings in the query case when, which are 'NONE','PLC'.

declare @table table (id int,string varchar(777))
insert into @table
select 1,'FINAL' union all
select 1,'PTE' union all
select 1,'FTE' union all
select 2,'PTE' union all
select 2,'FTE' union all
select 2,'NONE union all
select 3,'PTE' union all
select 3,'PLC' union all
select 3,'FTE'

select t.id,t.string from
(select row_number() over(partition by id order by case when string = 'final' then 0 when string = 'PTE' then 1 when string = 'fte' then 2 end) as rowno,* from @table ) t
where t.rowno = 1

Thank you very much for the help.

quote:

Originally posted by Jai Krishna


declare @table table (id int,string varchar(777))
insert into @table
select 1,'FINAL' union all
select 1,'PTE' union all
select 1,'FTE' union all
select 2,'PTE' union all
select 2,'FTE' union all
select 3,'FTE' 

select t.id,t.string from 
(select row_number() over(partition by id order by case when string = 'final' then 0 when string = 'PTE' then 1 when string = 'fte' then 2 end) as rowno,* from @table ) t
where t.rowno = 1

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:03:51

[code]
declare @table table (id int,string varchar(777))
insert into @table
select 1,'FINAL' union all
select 1,'PTE' union all
select 1,'FTE' union all
select 2,'PTE' union all
select 2,'FTE' union all
select 3,'FTE'

select t.id,t.string from
(select row_number() over(partition by id order by case when string = 'final' then 0 when string = 'PTE' then 1 when string = 'fte' then 2 else 3 end) as rowno,* from @table ) t
where t.rowno = 1
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:08:24

if you dont want to consider at all use this:-


declare @table table (id int,string varchar(777))
insert into @table
select 1,'FINAL' union all
select 1,'PTE' union all
select 1,'FTE' union all
select 2,'PTE' union all
select 2,'FTE' union all
select 3,'FTE' 

select t.id,t.string from 
(select row_number() over(partition by id order by case when string = 'final' then 0 when string = 'PTE' then 1 when string = 'fte' then 2 end) as rowno,* from @table where string not in ('NONE','PLC')) t
where t.rowno = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources