SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with GROUP BY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maharisi
Starting Member

19 Posts

Posted - 07/02/2007 :  08:43:17  Show Profile  Reply with Quote
Hello

020 20051101 20051130 11113
0X0 20051201 20051231 11113
020 20060101 20060131 11113
020 20060203 20060228 11113
020 20060301 20091231 11113
020 20100101 20700101 11113

HOW CAN I GROUP THIS RECORDS TO

020 20051101 20051130 11113
0X0 20051201 20051231 11113
020 20060101 20700101 11113

THANKS

Dallr
Yak Posting Veteran

87 Posts

Posted - 07/02/2007 :  08:52:12  Show Profile  Reply with Quote
From the results you posted i believe you want something like the following. I have used your data values as field names so just replace them with the relevant field name.

Select 020 , min(20060101) as MinDate, max(20070101) as MaxDate, 1113
From YourTableName
Group by 020,1113

Dallr

Go to Top of Page

Maharisi
Starting Member

19 Posts

Posted - 07/02/2007 :  09:11:47  Show Profile  Reply with Quote
Sorry I need Group by all groups according to atributes TYPE

TYPE A B C
020 20040101 20051031 11113
020 20051101 20051130 11113
0X0 20051201 20051231 11113
020 20060101 20060131 11113
020 20060203 20060228 11113
020 20060301 20091231 11113
020 20100101 20700101 11113

Group these records to

TYPE A B C
020 20040101 20051130 11113
0X0 20051201 20051231 11113
020 20060101 20700101 11113

Thanks a lot
Go to Top of Page

Maharisi
Starting Member

19 Posts

Posted - 07/02/2007 :  09:12:32  Show Profile  Reply with Quote
A,B,C are the next atributtes
Go to Top of Page

Dallr
Yak Posting Veteran

87 Posts

Posted - 07/02/2007 :  10:56:42  Show Profile  Reply with Quote
Sorry but i don't understand what you are asking. Can you please explain a bit more.

Dane
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/02/2007 :  11:59:12  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Maharisi -- as Dallr indicated, you have not provided enough information for us to assist you. You must explain to us the logic that is applied to generate those results, otherwise we are just guessing. If we guess, it takes longer and you will not get the right answer. If you provide specific details, there is no guessing involved and we can give you exactly what you want. Makes sense, right?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/02/2007 :  12:00:04  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Also, and this is just a guess (of course!), but this might help you:

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/02/2007 :  12:12:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or with SQL Server 2005,

SELECT Type, A, B, C FROM (
SELECT Type, A, B, C, ROW_NUMBER() OVER (PARTITION BY Type ORDER BY A) AS RecID) AS d
WHERE RecID = 1



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Maharisi
Starting Member

19 Posts

Posted - 07/03/2007 :  07:27:14  Show Profile  Reply with Quote
Thanks you Peso

But I dont have SQL SERVER 2005. I have 2000
Function ROW_NUMBER is not provided in.
Have you another idea?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/03/2007 :  09:38:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- prepare sample data
create table	#sample
		(
			a varchar(3),
			t1 smalldatetime,
			t2 smalldatetime,
			b smallint
		)

insert	#sample
select	'020', '20060301', '20091231', 11113 union all
select	'020', '20051101', '20051130', 11113 union all
select	'020', '20060101', '20060131', 11113 union all
select	'0X0', '20051201', '20051231', 11113 union all
select	'020', '20060203', '20060228', 11113 union all
select	'020', '20100101', ' 20700101', 11113

-- set up sample date in order
create unique clustered index ix_sample on #sample (t1, a)
alter table #sample add grp int
go

-- update grp#
declare	@grp int,
	@last varchar(3)

update	#sample
set	@grp = grp = case when isnull(@last, '') <> a then isnull(@grp, 0) + 1 else @grp end,
	@last = a

-- show the expected output
select		a,
		min(t1) as t1,
		max(t2) as t2
from		#sample
group by	a,
		grp
order by	2

alter table #sample drop column grp
go

select * from #sample

drop table #sample


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

facestheband
Starting Member

USA
6 Posts

Posted - 07/05/2007 :  00:22:44  Show Profile  Visit facestheband's Homepage  Reply with Quote
You may not need a group by here at all if you don't need a count.


write the following
select distinct field1, field2, field3 from tablename

if you need a count at the end of each row then write

select field1, field2, field3, count(*) from tablename group by field1, field2, field3
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/05/2007 :  02:34:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by facestheband

You may not need a group by here at all if you don't need a count.


write the following
select distinct field1, field2, field3 from tablename

if you need a count at the end of each row then write

select field1, field2, field3, count(*) from tablename group by field1, field2, field3


Is this related to this thread?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Maharisi
Starting Member

19 Posts

Posted - 07/12/2007 :  05:10:13  Show Profile  Reply with Quote
Thanks guys,
Really great ideas, especially Peter Larsson
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.11 seconds. Powered By: Snitz Forums 2000