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

SQL Server 2005 Forums

Transact-SQL (2005)

group by

Author

Topic

fanfan2
Starting Member

5 Posts

Posted - 2010-11-05 : 13:51:31

I have the following data:

F1 F2 F3 F4
A 0 1 a
A 1 2 a
A 2 3 b
A 3 4 a
B 1 3 b
B 3 5 b
B 5 6 d
C 2 4 d
C 4 6 v
C 6 7 v
C 7 8 t

I want the results to be:

F1 F2 F3 F4
A 0 2 a
A 2 3 b
A 3 4 a
B 1 5 b
B 5 6 d
C 2 4 d
C 4 7 v
C 7 8 t

what I wrote was:

SELECT F1, Min(F2), Max(F3), F4
FROM table1
GROUP BY F1, F4

I didn't get the right results as it seems to have selected the min and max first and then grouped by, but I wanted to group by F1 F4 first and then pick the min and max. What should I write? Thanks very much in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 13:58:14

Don't know why you think that you should get that posted result.
One row then should be A 0 4 a and that's correct.

I can't see a rule that should bring your wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

fanfan2
Starting Member

5 Posts

Posted - 2010-11-05 : 14:10:44

I want to group by F1 and F4, and within that group by pick the min of F2 and max of F3. so the first results should be A 0 2 a, not A 0 4 a. But when I run my code it gives A 0 4 a.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-05 : 14:16:27

See your posted example data:
A 0 1 a
A 1 2 a
A 2 3 b
A 3 4 a

When grouping the A 2 3 b isn't part of the group from A to a. So it is
A 0 1 a
A 1 2 a
A 3 4 a

edit: typo

No, you're never too old to Yak'n'Roll if you're too young to die.

fanfan2
Starting Member

5 Posts

Posted - 2010-11-05 : 14:26:09

Oh yes thanks very much - you are right I miss understood the group by.
Then is there a way that I could get the results that only when F4 are consecutively the same then group by together?
A 0 2 a
A 2 3 b
A 3 4 a

sandeepKM
Starting Member

1 Post

Posted - 2010-11-15 : 01:45:16

Try This...

declare @tb table( slno numeric(10,0), F1 varchar(50), F2 varchar(50), F3 varchar(50), F4 varchar(50))
insert into @tb select * from (SELECT ROW_NUMBER() OVER (ORDER BY F1) AS RowNo, F1,F2,F3,F4 FROM TEST5) cur
select F1,min(F2) as F2,max(F3)as F3,F4 from(
select F1,F2,F3,F4,(case F4 WHEN (select F4 from @tb where slno= t.slno-1 ) then slno-1 else slno end)temp from @tb t
)cur
group by temp,F1,f4

~~~ Enjoy Knowledge freedom ~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources