Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Group by clause on partial select Expand / Collapse
Author
Message
Posted Friday, November 06, 2009 3:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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
Post #815230
Posted Friday, November 06, 2009 4:04 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
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 2008, MVP
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

Post #815238
Posted Friday, November 06, 2009 4:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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.
Post #815247
Posted Friday, November 06, 2009 10:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
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 2008, MVP
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

Post #815324
Posted Saturday, November 07, 2009 1:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #815352
Posted Monday, November 09, 2009 8:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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.
Post #815843
Posted Monday, November 09, 2009 12:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:51 AM
Points: 858, Visits: 608
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



Post #816043
Posted Monday, November 09, 2009 3:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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?
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816165
Posted Tuesday, November 10, 2009 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 41,569, Visits: 34,494
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 2008, MVP
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

Post #816444
Posted Wednesday, January 04, 2012 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:40 PM
Points: 159, Visits: 454
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
Post #1230239
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse