December 2, 2012 at 9:29 am
hi friend i have a small doubt in sql server plz tell me how to solve
table data contins like id,name,sal
1,abc,1200
2,jaid,5300
3,union,1000
4,ravod.200
i want output like
id , name, sal , max(sal), min(sal)
1 ,abc ,1200 , null , null
2 ,jaid ,5300 , yes , null
3,union , 1000 , null ,null
4,ravod ,200 ,null , yes
when ever max value find that time display yes other wise display null values and when ever min valu find that time disply yes other wise display null values .plz tell me how to write query in sql server
December 2, 2012 at 9:54 am
asranantha (12/2/2012)
hi friend i have a small doubt in sql server plz tell me how to solvetable data contins like id,name,sal
1,abc,1200
2,jaid,5300
3,union,1000
4,ravod.200
i want output like
id , name, sal , max(sal), min(sal)
1 ,abc ,1200 , null , null
2 ,jaid ,5300 , yes , null
3,union , 1000 , null ,null
4,ravod ,200 ,null , yes
when ever max value find that time display yes other wise display null values and when ever min valu find that time disply yes other wise display null values .plz tell me how to write query in sql server
This should do what you want:
/*
table data contins like id,name,sal
1,abc,1200
2,jaid,5300
3,union,1000
4,ravod.200
i want output like
id , name, sal , max(sal), min(sal)
1 ,abc ,1200 , null , null
2 ,jaid ,5300 , yes , null
3,union , 1000 , null ,null
4,ravod ,200 ,null , yes
*/
create table #TestTable(
id int,
name varchar(10),
salary int
);
go
insert into #TestTable
values
(1,'abc',1200),
(2,'jaid',5300),
(3,'union',1000),
(4,'ravod',200);
go
with MaxSalary as (
select
max(salary) MaxSalary
from
#TestTable
), MinSalary as (
select
min(salary) MinSalary
from
#TestTable
)
select
id,
name,
salary,
case when MaxSalary is not null then 'Yes' end MaxSalary,
case when MinSalary is not null then 'Yes' end MinSalary
from
#TestTable tt
left outer join MaxSalary ms1
on (tt.salary = ms1.MaxSalary)
left outer join MinSalary ms2
on (tt.salary = ms2.MinSalary)
go
drop table #TestTable;
go
December 2, 2012 at 10:43 am
thanks its working fine. in this query i want modify when max salary then display yes and remaing sal column display no and when min salary then display yes and remaing sal columns no.
pls tell me query
December 2, 2012 at 11:02 am
asranantha (12/2/2012)
thanks its working fine. in this query i want modify when max salary then display yes and remaing sal column display no and when min salary then display yes and remaing sal columns no.pls tell me query
Don't think so. You should be able to figure this out based on what I have already provided.
It is a minor change to what I provided.
December 2, 2012 at 9:14 pm
k i got it
December 2, 2012 at 10:42 pm
asranantha (12/2/2012)
k i got it
Are you sure? Post the code with your changes.
December 3, 2012 at 12:33 am
just we add else statement here
case when MaxSalary is not null then 'Yes' else 'no' end MaxSalary,
case when MinSalary is not null then 'Yes' else 'no' end MinSalary
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply