Grouping result according to one column in sql

I have a sql statement like

SELECT column1, column2,column3, colu开发者_开发技巧mn4  FROM table1 GROUP BY column3

or

 SELECT *  FROM table1 GROUP BY column3

I want a result which is grouped according to column3 and also has other columns. But it gives an error.What should I do? thanks..

If i understand your expected end result correctly, try using ORDER instead of GROUP

if however that does not give you your desired output, i apologize.

SELECT column1, column2,column3, column4
  FROM table1
 ORDER BY column3

SELECT *
  FROM table1
 ORDER BY column3

What you are trying to do is an undefined behavior. When you specify GROUP BY on a column, there can be multiple values in other columns. There is no sane way for the database to figure out what values to show.

PARTITION is probably what you are looking for. Check this link: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Grouping applies to aggregate functions such as SUM(). It doesn't make sense to group individual fields.

SELECT SUM(col1), MAX(col2), col3 from t1 group by col3

I haven't understood your question exactly and what for you need this query, but maybe you can use this one:

/*t-sql example*/

create table #T (id int identity(1,1), col1 varchar(5), col2 varchar(5), col3 varchar(6))

insert #T (col1,col2,col3) values ('aaa','a1e','group1')
insert #T (col1,col2,col3) values ('bbb','a2e','group1')
insert #T (col1,col2,col3) values ('ccc','a3e','group1')
insert #T (col1,col2,col3) values ('ddd','a4e','group2')
insert #T (col1,col2,col3) values ('eee','a5e','group2')
insert #T (col1,col2,col3) values ('fff','a6e','group3')


select id,col1,col2,#T.col3,subq.cnt from #T
join 
(select col3, COUNT(*) as 'cnt' from #T group by col3) as subq
on 
#T.col3 = subq.col3

I have used sub-query, but you can use temporary table or WITH. Also, I have used "count" for aggregation just for example.

Max