Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql query implementaion Expand / Collapse
Author
Message
Posted Sunday, December 2, 2012 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
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
Post #1391733
Posted Sunday, December 2, 2012 9:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,704, Visits: 32,349
asranantha (12/2/2012)
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


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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391734
Posted Sunday, December 2, 2012 10:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
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
Post #1391736
Posted Sunday, December 2, 2012 11:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,704, Visits: 32,349
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391739
Posted Sunday, December 2, 2012 9:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
k i got it
Post #1391766
Posted Sunday, December 2, 2012 10:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,704, Visits: 32,349
asranantha (12/2/2012)
k i got it


Are you sure? Post the code with your changes.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1391771
Posted Monday, December 3, 2012 12:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
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
Post #1391789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse