November 6, 2009 at 3:54 pm
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
November 6, 2009 at 4:04 pm
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
November 6, 2009 at 4:19 pm
Gail,
Here is my data:
TestRoomtest session 22009-11-15 08:00:00.000AhmadAwada
TestRoomtest session 22009-11-15 08:00:00.000AhmadAwada
TestRoomtest session 22009-11-15 08:00:00.000AlastairKyle
TestRoomtest session 22009-11-15 08:00:00.000AlastairKyle
Veteran-s Educational 2009-11-15 13:30:00.000DavidRimm
Veteran-s Educational 2009-11-15 13:30:00.000Frédérique Penault-Llorca
Veteran-s Educational 2009-11-15 13:30:00.000GiuseppeViale
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.
November 6, 2009 at 10:56 pm
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
November 7, 2009 at 1:43 am
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
SQLkiwi blog
@SQL_Kiwi
November 9, 2009 at 8:52 am
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.
November 9, 2009 at 12:26 pm
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.
SELECTd.room_name,
d.session_name,
d.event_date,
d.delegate
FROM@data d
JOIN (SELECTroom_name,
session_name,
event_date,
delegate
FROM@data
GROUP BY
room_name,
session_name,
event_date,
delegate
HAVINGCOUNT(*) > 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
November 9, 2009 at 3:06 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 10, 2009 at 6:40 am
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
January 4, 2012 at 12:32 pm
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
January 4, 2012 at 12:43 pm
You are responding to a thread which is 2 years old!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply