Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Apr 13, 2012 07:46 AM by silvioyf
Member
295 Points
127 Posts
Apr 13, 2012 04:50 AM|LINK
hi,
i have query like this select reference_no from table_name order by reference_no it;s work well
but
if m write select distinct refernce_no from table_name order by referene_no it;s show error during execute.
Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
sqlserver
Contributor
6767 Points
1057 Posts
Apr 13, 2012 04:56 AM|LINK
because of incorrect spelling of column user in order by "referene_no" should be "refernce_no"
2805 Points
534 Posts
Apr 13, 2012 05:04 AM|LINK
Use
select distinct refernce_no from table_name order by 1
Apr 13, 2012 05:07 AM|LINK
here write some written mistake but actually not run
select reference_no from table_name order by substring(reference_no,17,4) it's work well reference_no like DAR/EXP/FCL/2012-0051
select distinct reference_no from table_name order by substring(reference_no,17,4) it's show error reference_no like DAR/EXP/FCL/2012-0051
361 Points
238 Posts
Apr 13, 2012 06:45 AM|LINK
Use this:
select distinct reference_no, substring(reference_no,17,4) from table_name order by substring(reference_no,17,4)
If you use SELECT DISTINCT and ORDER BY, ORDER BY item must be in the SELECT list.
Apr 13, 2012 07:04 AM|LINK
If you want to output reference_no only, use this:
SELECT reference_no FROM ( select distinct reference_no, substring(reference_no,17,4) as order_list from table_name) tmp order by order_list
Participant
1754 Points
340 Posts
Apr 13, 2012 07:46 AM|LINK
You can also try:
select * from (select distinct reference_no from table_name) as t order by substring(reference_no,17,4)
I hope this helps.
Twinkle1989
Member
295 Points
127 Posts
Select Query with distinct and order by.
Apr 13, 2012 04:50 AM|LINK
hi,
i have query like this select reference_no from table_name order by reference_no it;s work well
but
if m write select distinct refernce_no from table_name order by referene_no it;s show error during execute.
Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
sqlserver
Twinkle Arora (India)
sandeepmitta...
Contributor
6767 Points
1057 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 04:56 AM|LINK
because of incorrect spelling of column user in order by "referene_no" should be "refernce_no"
Sandeep Mittal | My Blog - IT Developer Zone
gaikwad_anil...
Contributor
2805 Points
534 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 05:04 AM|LINK
Use
select distinct refernce_no from table_name order by 1
sqlserver
www.thecodekey.com
Please mark as answer if useful
Twinkle1989
Member
295 Points
127 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 05:07 AM|LINK
here write some written mistake but actually not run
select reference_no from table_name
order by substring(reference_no,17,4) it's work well reference_no like DAR/EXP/FCL/2012-0051
select distinct reference_no from table_name
order by substring(reference_no,17,4) it's show error reference_no like DAR/EXP/FCL/2012-0051
Twinkle Arora (India)
Phinehas
Member
361 Points
238 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 06:45 AM|LINK
Use this:
select distinct reference_no, substring(reference_no,17,4) from table_name
order by substring(reference_no,17,4)
If you use SELECT DISTINCT and ORDER BY, ORDER BY item must be in the SELECT list.
Phinehas
Member
361 Points
238 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 07:04 AM|LINK
If you want to output reference_no only, use this:
SELECT reference_no FROM (
select distinct reference_no, substring(reference_no,17,4) as order_list from table_name) tmp
order by order_list
silvioyf
Participant
1754 Points
340 Posts
Re: Select Query with distinct and order by.
Apr 13, 2012 07:46 AM|LINK
You can also try:
I hope this helps.