GROUP BY and ORDER BY

hi Guys!
I am having a table
customer {
Name varchar(255),
step int,
details varchar(255)
}
I am trying to get the list of customer name with GROUP BY, and order
it using ORDER BY
SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
any suggestions?Hi
You'll have to include a step column within a SELECT stratement (at least)
and then ORDER BY this column
<sharma.vasudev@.gmail.com> wrote in message
news:1133327048.081452.157790@.z14g2000cwz.googlegroups.com...
> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||Hi Vasudev,
There is a problem with Table design, looking at the query you are executing
.
Try the following query. Please note that I am using Step in select list
(for same error u got).
SELECT Distinct Name,Step FROM customer ORDER BY step
Vishal Khajuria
9886170165
IBM Bangalore
"sharma.vasudev@.gmail.com" wrote:

> hi Guys!
> I am having a table
> customer {
> Name varchar(255),
> step int,
> details varchar(255)
> }
> I am trying to get the list of customer name with GROUP BY, and order
> it using ORDER BY
> SELECT Name FROM customer GROUP BY Name ORDER BY step
> but cause of ORDER BY clause it fails with
> Column name 'customer.step' is invalid in the ORDER BY clause because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> any suggestions?
>|||ok, i did that with following query
SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?|||hi Vishal,
i am really new with SQL stuff, kinda newbie, I am not sure what you
are pin-pointing to when you said 'There is a problem with Table
design' i would really appreciate if you could explain me a bit?
/dev|||To make things easy for everyone, can you just post some sample rows and
expected result?
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"hack_tick" <sharma.vasudev@.gmail.com> wrote in message
news:1133336153.369184.296940@.o13g2000cwo.googlegroups.com...
> ok, i did that with following query
> SELECT Name, step from customer group by Name order by step
> it came out with following error,
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'customer.step is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> so i also tried GROUP BY
> SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
> but as expected it returned the result which was of no use to me :(
> any suggestions?
>|||Hi Vasudev,
The point to be noticed here is that the table does not conform to rules of
normalization. You should have one more table for Name of customers.
As per your question you were trying to get list of cusotmer names by using
Group By clause --
I did not see any reason why do you need group by clause.
What I could guess is that you want distinct names of customer, that is why
I suggested query with distinct and not group by. But here also you need to
keep step in Select clause.
Please let me know if you have any more questions.
--
Vishal Khajuria
9886170165
IBM Bangalore
"hack_tick" wrote:

> hi Vishal,
> i am really new with SQL stuff, kinda newbie, I am not sure what you
> are pin-pointing to when you said 'There is a problem with Table
> design' i would really appreciate if you could explain me a bit?
> /dev
>|||hi Vishal
The Table used was just for explanation, I am having a much bigger
table with 100's of columns and many complex relationship, also the
format of table is fixed and not suppose to change :(
I shall post a complete test case with insert in a while :)|||hi guys! maybe you all can have a look at the following query!
I need to have the name of the customer displayed, but they have to be
order by column 'step'
create table customer (
name varchar(255),
step int,
details varchar(255)
)
insert into customer values('cust-1', 1, 'details-1')
insert into customer values('cust-2', 2, 'details-2')
insert into customer values('cust-2', 3, 'details-3')
insert into customer values('cust-2', 4, 'details-4')
insert into customer values('cust-3', 5, 'details-5')
insert into customer values('cust-3', 6, 'details-6')
insert into customer values('cust-3', 3, 'details-7')
insert into customer values('cust-1', 4, 'details-8')
insert into customer values('cust-4', 5, 'details-9')
insert into customer values('cust-4', 1, 'details-10')
insert into customer values('cust-5', 1, 'details-11')
insert into customer values('cust-6', 3, 'details-11')
select name from customer group by name
i have tried following queries with following error
1) SELECT Name FROM customer GROUP BY Name ORDER BY step
but cause of ORDER BY clause it fails with
Column name 'customer.step' is invalid in the ORDER BY clause because
it is not contained in either an aggregate function or the GROUP BY
clause.
2) SELECT Name, step from customer group by Name order by step
it came out with following error,
Server: Msg 8120, Level 16, State 1, Line 1
Column 'customer.step is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
so i also tried GROUP BY
SELECT Name, step FROM customer GROUP BY Name, step ORDER BY step
but as expected it returned the result which was of no use to me :(
any suggestions?
PS: Sorry for any redundant data from my earlier post, just wanted to
have a common place for your all to look at :)|||what I want is to have the List of UNIQUE Customer name, but they have
to be sorted using field 'step'