Need help with ORDER BY and GROUP BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with ORDER BY and GROUP BY

Author

Topic

Flame113
Starting Member

5 Posts

Posted - 2009-11-23 : 22:26:13

Hi all,
I want to create a script that select data from database and display as a list. For example: a list of countries which US is always on top.
Here is my script:

SELECT countryCode, countryDescription, SUM(deleted) as deleted
FROM
(SELECT .... FROM ...)
GROUP BY countryCode, countryDescription

The problem is: if I put these codes inside (SELECT ....), it will work but when I execute all statements the order doesn't change at all:

ORDER BY
CASE countryDescription
WHEN 'US' THEN 0
ELSE 1
END
ASC

Sorry for my bad English. Thanks :)

Vu Minh Hai

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-23 : 23:47:01

Could you show us a data example of what you mean?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Flame113
Starting Member

5 Posts

Posted - 2009-11-24 : 02:08:21

Thanks for your quick reply!
Here is the example:
|-------------------------------------------|
|countryCode | countryDescription | deleted |
| 1 | Algeria | 1 |
| 2 | Canada | 3 |
| 3 | Egypt | 0 |
| 4 | United States | 0 |
| 5 | Wales | 2 |

And I want to display like this:

|-------------------------------------------|
|countryCode | countryDescription | deleted |
| 4 | United States | 0 |
| 1 | Algeria | 1 |
| 2 | Canada | 3 |
| 3 | Egypt | 0 |
| 5 | Wales | 2 |

Thank you again :)

Vu Minh Hai

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-24 : 02:20:47

This can be easily done with the help of Cursor..
if you want it to do in select query means, give soe more time let me try out and reply you..

Balaji.K

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-24 : 02:34:07

use order by clause for final select statement

SELECT countryCode, countryDescription, SUM(deleted) as deleted
FROM
(SELECT .... FROM ...)
GROUP BY countryCode, countryDescription
ORDER BY
CASE countryDescription
WHEN 'US' THEN 0
ELSE 1
END
ASC

Flame113
Starting Member

5 Posts

Posted - 2009-11-24 : 03:16:48

@bklr: yeah, that is what i tried and I got this error message:
ORDER BY column or expression must be in SELECT list in this context.

Vu Minh Hai

Flame113
Starting Member

5 Posts

Posted - 2009-11-24 : 22:38:13

Can anyone help me pls?

Vu Minh Hai

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-24 : 22:43:30

Show us the exact code that you ran when you got that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Flame113
Starting Member

5 Posts

Posted - 2009-11-24 : 23:41:05

SELECT industryCode,
industryDescription,
SUM(response) as response,
SUM(non_response) as non_response,
SUM(wrong_address) as wrong_address,
SUM(closure) as closure,
SUM(deleted) as deleted
FROM
(SELECT
tbl_industry.industry_code industryCode,
tbl_industry.industry_description industryDescription,
CASE
when (response_status = "Response") AND (tbl_survey.company_status = '') THEN 1
else 0
END response,
CASE
when (response_status = "No Response") AND (tbl_survey.company_status = '') THEN 1
else 0
END non_response,
CASE
when (response_status = "Wrong Address") AND (tbl_survey.company_status = '') THEN 1
else 0
END wrong_address,
CASE
when (tbl_survey.company_status like "C%") THEN 1
else 0
END closure,
CASE
when (tbl_survey.company_status = "DELETED") THEN 1
else 0
END deleted
FROM tbl_survey
LEFT JOIN tbl_directory
ON tbl_directory.serial = tbl_survey.serial
JOIN tbl_company
ON tbl_company.tbl_company_id = tbl_survey.tbl_company_id
LEFT JOIN tbl_industry
ON tbl_industry.industry_code = tbl_directory.indgrp
LEFT JOIN (select ref_item_code, ref_item_desc response_status
FROM pla_piapr_reference
WHERE ref_group_code = 'RES') response_table
ON tbl_directory.respon = response_table.ref_item_code
) GROUP BY industryCode, industryDescription

ORDER BY
CASE industryDescription
WHEN 'Misc' THEN 0
ELSE 1
END
ASC

Vu Minh Hai

dattatreysindol
Starting Member

20 Posts

Posted - 2009-11-24 : 23:50:13

Try this...

SELECT Tmp.CountryCode, Tmp.CountryDescription, Tmp.Deleted INTO #TempTable FROM (
SELECT 1 AS CountryCode, 'Algeria' AS CountryDescription, 1 AS Deleted
UNION
SELECT 2 AS CountryCode, 'Canada' AS CountryDescription, 3 AS Deleted
UNION
SELECT 3 AS CountryCode, 'Egypt' AS CountryDescription, 0 AS Deleted
UNION
SELECT 4 AS CountryCode, 'United States' AS CountryDescription, 0 AS Deleted
UNION
SELECT 5 AS CountryCode, 'Wales' AS CountryDescription, 2 AS Deleted
) Tmp

SELECT CountryCode, CountryDescription, Deleted
FROM #TempTable
ORDER BY
CASE CountryDescription
WHEN 'United States' THEN 0
WHEN 'Algeria' THEN 1
WHEN 'Canada' THEN 2
WHEN 'Egypt' THEN 3
WHEN 'Wales' THEN 4
END

Hope this helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources