t-sql

I have a database table in following format

GID ID VALUE

1 1 TRUE

1 2 TRUE

1 3 TRUE

2 4 FALSE

2 5 TRUE

3 6TRUE

What is want is it should groupby GID and shold display like this

GID VALUE

1 TRUE

2 FALSE

3 TRUE

i.e. for each GID TRUE is returned if each of its ID is TRUE, otherwise returns FALSE.

How to do this using SQL or T-SQL whatever........

March 7th, 2008 11:17am

You can use the MIN() function...

Code Snippet

DECLARE @t TABLE (GID int, Value varchar(10))

INSERT @t (GID, Value)

VALUES (1, 'True')

INSERT @t (GID, Value)

VALUES (1, 'True')

INSERT @t (GID, Value)

VALUES (1, 'True')

INSERT @t (GID, Value)

VALUES (2, 'False')

INSERT @t (GID, Value)

VALUES (2, 'True')

SELECT *

FROM @t

SELECT GID, MIN(Value)

FROM @t

GROUP BY GID

Free Windows Admin Tool Kit Click here and download it now
March 7th, 2008 11:23am

Which version of SQL server you are using?

Here is code that will work in SQL2k5 and 2k.. .. you can also use CTE in SQL 2k5.

Code Snippet

Declare @Test table (GID int, ID int, Value varchar(10))

Insert Into @Test

Select 1, 1, 'TRUE' Union All

Select 1, 2, 'TRUE' Union All

Select 1, 3, 'TRUE' Union All

Select 2, 4, 'FALSE' Union All

Select 2, 5, 'TRUE' Union All

Select 3, 6, 'TRUE'

Select Distinct GID, Value

From @Test

Where Value = 'FALSE' UNION ALL

Select Distinct GID, Value

From @Test

Where Value = 'TRUE'

and GID Not In (Select GID

From @Test

Where Value = 'FALSE')

Order by 1

March 7th, 2008 11:30am


create table #temptable(
gid int ,
id int identity(1,1) ,
record varchar(10)
)


insert into #temptable(gid,record) values(3,'True')

select t.gid,t.id,t.record
from
(select gid,id,record,rows=row_number() over (partition by gid order by gid asc) from #temptable) t
where t.rows=1
Free Windows Admin Tool Kit Click here and download it now
March 7th, 2008 12:36pm

Atlast i got a solution for this,

Code Snippet

DECLARE @t TABLE (GID int, Value varchar(10))

INSERT @t (GID, Value) VALUES (1, 'True')

INSERT @t (GID, Value) VALUES (1, 'True')

INSERT @t (GID, Value) VALUES (1, 'True')

INSERT @t (GID, Value) VALUES (2, 'False')

INSERT @t (GID, Value) VALUES (2, 'True')

INSERT @t (GID, Value) VALUES (3, 'True')

INSERT @t (GID, Value) VALUES (4, 'False')

INSERT @t (GID, Value) VALUES (5, 'True')

(select distinct t1.GID,t1.Value from @t as t1 inner join @t as t2 on t1.GID=t2.GID

where t1.gid not in (select t3.gid from @t as t3 where t3.value ='false'))

union

(select distinct t3.GID,t3.Value from @t as t3 where t3.value ='false')

select * from @t

Regards,

Venkatesan Prabu . J

March 8th, 2008 1:26pm

Just a play around with bit operators

Code Snippet

Declare @Test table (GID int, ID int, Value varchar(10))

Insert Into @Test

Select 1, 1, 'TRUE' Union All

Select 1, 2, 'TRUE' Union All

Select 1, 3, 'TRUE' Union All

Select 2, 4, 'FALSE' Union All

Select 2, 5, 'TRUE' Union All

Select 3, 6, 'FALSE' Union All

Select 3, 7, 'FALSE' Union All

Select 4, 8, 'TRUE'

SELECT

GID

,CASE

WHEN ~((~CAST(MAX(Value) AS BIT)) | (~CAST(MIN(Value) AS BIT))) = 0 THEN 'FALSE'

ELSE 'TRUE'

END AS 'VALUE'

FROM @Test

GROUP BY GID

Free Windows Admin Tool Kit Click here and download it now
March 8th, 2008 5:03pm

If the column type is bit then:
SELECT GID,
CAST(MIN(CAST(Value AS TINYINT)) AS BIT) AS Value
FROM @Test
GROUP BY GID

if it's character type then
SELECT GID,
MIN(Value) AS Value
FROM @Test
GROUP BY GID
September 26th, 2009 10:24am

So easy! You need to do this. (:

with [database] (GID,ID,VALUE)
as
(
	select 1,1,'TRUE'
	union all
	select 1,2,'TRUE'
	union all
	select 1,3, 'TRUE'
	union all 
	select 2,4, 'FALSE'
	union all 
	select 2,5,'TRUE'
	union all 
	select 3,6,'TRUE'

)


select 
		
			GID,
			VALUE
from 
			[database] d
		where 
			id =(select min(id) from [database] where GiD=d.GID)
			 
		

Free Windows Admin Tool Kit Click here and download it now
April 1st, 2015 7:49pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics