Problem with GROUP BY - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem with GROUP BY

Author

Topic

Maharisi
Starting Member

19 Posts

Posted - 2007-07-02 : 08:43:17

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 - 2007-07-02 : 08:52:12

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

Maharisi
Starting Member

19 Posts

Posted - 2007-07-02 : 09:11:47

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

Maharisi
Starting Member

19 Posts

Posted - 2007-07-02 : 09:12:32

A,B,C are the next atributtes

Dallr
Yak Posting Veteran

87 Posts

Posted - 2007-07-02 : 10:56:42

Sorry but i don't understand what you are asking. Can you please explain a bit more.

Dane

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-02 : 11:59:12

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-02 : 12:00:04

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-02 : 12:12:56

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

Maharisi
Starting Member

19 Posts

Posted - 2007-07-03 : 07:27:14

Thanks you Peso

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 09:38:42

[code]-- 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[/code]

Peter Larsson
Helsingborg, Sweden

facestheband
Starting Member

6 Posts

Posted - 2007-07-05 : 00:22:44

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 02:34:49

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

Maharisi
Starting Member

19 Posts

Posted - 2007-07-12 : 05:10:13

Thanks guys,
Really great ideas, especially Peter Larsson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources