Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group by clause on partial select


Group by clause on partial select

Author
Message
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 690
Is it possible to have a GROUP BY CLAUSE on only some selected fields?
For example:

SELECT col1, col2, col3
FROM TableA
GROUP BY col1
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54117 Visits: 44634
Only if all other columns are part of aggregate expressions. The query you have there is not valid. This would be.
SELECT col1, max(col2), Max(col3)
FROM TableA
GROUP BY col1



Think about it. You're asking for 1 row for each value that's in Col1 (that's what group by means). SQL has to know what to do with the multiple values of col2 and col3.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 690
Gail,
Here is my data:

TestRoom test session 2 2009-11-15 08:00:00.000 Ahmad Awada
TestRoom test session 2 2009-11-15 08:00:00.000 Ahmad Awada
TestRoom test session 2 2009-11-15 08:00:00.000 Alastair Kyle
TestRoom test session 2 2009-11-15 08:00:00.000 Alastair Kyle
Veteran-s Educational 2009-11-15 13:30:00.000 David Rimm
Veteran-s Educational 2009-11-15 13:30:00.000 Frédérique Penault-Llorca
Veteran-s Educational 2009-11-15 13:30:00.000 Giuseppe Viale

I need to get the first 4 records in a result set . How do i do it?
I cannot select top 4 because after the date expires it could be 1 record or it could be 5.

Thanks for your help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54117 Visits: 44634
I don't understand what controls the rows that you want. Do you want the top 4 records, the top some other, unique values by some column, or something else.

Please read through this and post the table structure and desired results
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11098 Visits: 11353
Here's a script to aid the discussion:


declare @data
table
(
room_name varchar(20) not null,
session_name varchar(20) not null,
event_date datetime not null,
delegate varchar(30) not null
);

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 08:00:00', 'Ahmad Awada');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 08:00:00', 'Ahmad Awada');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 08:00:00', 'Alastair Kyle');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 08:00:00', 'Alastair Kyle');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 13:30:00', 'David Rimm');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 13:30:00', 'Frédérique Penault-Llorca');

insert @data (room_name, session_name, event_date, delegate)
values ('Test Room', 'Test Session', '2009-11-15 13:30:00', 'Giuseppe Viale');

-- Idea 1
with NumberedRows
as (
select *,
rn = ROW_NUMBER() OVER (PARTITION BY event_date ORDER BY delegate)
from @data
)
select event_date, room_name, session_name, delegate
from NumberedRows
where rn <= 4;

-- Idea 2
select D.event_date, CA.room_name, CA.session_name, CA.delegate
from (
select event_date
from @data
group by event_date
) D
cross
apply (
select top (4)
*
from @data D2
where D2.event_date = D.event_date
order by
delegate
) CA;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
ramadesai108
ramadesai108
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 690
Thanks Paul and Gail for taking time. In this case what I will have to do is to insert the records into a temp table and thru a cursor check the previous Key value with the current one and if they are the same then insert the record into the temp table otherwise not. I was looking for a easier way, but i guess there is no easy way.

Thanks again.
dsdeming
dsdeming
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 647
I may have misunderstood what you were looking for.

This should get you the first four rows you wanted. The derived table x will include distinct copies of only those rows that are duplicates. You have to join back to the sopurce table to get all the rows.

SELECT d.room_name,
d.session_name,
d.event_date,
d.delegate
FROM @data d
JOIN ( SELECT room_name,
session_name,
event_date,
delegate
FROM @data
GROUP BY
room_name,
session_name,
event_date,
delegate
HAVING COUNT(*) > 1) x
ON d.room_name = x.room_name
AND d.session_name = x.session_name
AND d.event_date = x.event_date
AND d.delegate = x.delegate



Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11098 Visits: 11353
ramadesai108 (11/9/2009)
Thanks Paul and Gail for taking time. In this case what I will have to do is to insert the records into a temp table and thru a cursor check the previous Key value with the current one and if they are the same then insert the record into the temp table otherwise not. I was looking for a easier way, but i guess there is no easy way.

Am I alone in wondering how we were supposed to guess that from the original question? :-D
There are in fact easier ways - and a cursor is not required.
If you post some proper sample data and expected output, one of us will be sure to provide an awesome script for you. I'm a bit busy at work at the moment, but will try to look by later.
Solutions generally involve the ROW_NUMBER() window aggregate...



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54117 Visits: 44634
ramadesai108 (11/9/2009)
In this case what I will have to do is to insert the records into a temp table and thru a cursor check the previous Key value with the current one


No, I'm pretty sure you don't have to do that.

I never said there's no way, I said i don't understand your requirements. Explain more (and not with something like "top 4 records") and you'll very likely get a well-performing, easy solution. If I have to guess what you want, that's a lot less likely.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kevin_nikolai
kevin_nikolai
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 511
ramadesai108:
Is it possible to have a GROUP BY CLAUSE on only some selected fields, For example:
--------------------------
if col3 has an aggregrate (sum, count, etc.), and the rest don't, then the rest of columns
need to be added to GROUP BY clause.
example: SELECT col1, col2, sum(col3) FROM TableA GROUP BY col1, col2

if none of the columns has an aggregate, then you can omit the GROUP BY clause.

you can also use the GROUP BY clause instead of DISTINCT.
example: SELECT DISTINCT col1, col2, col3 FROM TableA
example: SELECT col1, col2, col3 FROM TableA GROUP BY col1, col2, col3
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search