SQL help

[Goran Stankovic]

Hello everyone,

I have a problem with order by clause.
My table has 2 columns: type and country.

select type, country from test_pnl_report

type country
----------- --------------
Bank Debt United States
Bond Future United States
Bond Future United States
Bond Future New Zealand
Bond Future Canada
Bond Future United States
Bond Future Japan
Bond Future United States
Bond Future Japan
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future Germany
Bond Future Spain
Bond Future Canada
Bond Future Canada
Bond Future United States
Bond Future United States
Bond Future Canada
Bond Future Spain
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future Spain
Bond Future United States
Bond Future United Kingdom
Bond Future New Zealand
Bond Future Japan
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States

When I use following code

select type, country from test_pnl_report
group by type
order by type

I got country column grouped in some strange algorithm. I expect to got entries type:country row by row
as they are in the database.

type country
----------- --------------
Bank Debt United States
Bond Future Japan
Bond Future Japan
Bond Future Japan
Bond Future Spain
Bond Future Spain
Bond Future Spain
Bond Future Canada
Bond Future Canada
Bond Future Canada
Bond Future Canada
Bond Future Germany
Bond Future New Zealand
Bond Future New Zealand
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United States
Bond Future United Kingdom

Is there any solution for this?
Thanks in advance.
Goran.

[Scott Selikoff]

What exactly is the problem? There's no such ordering as "as they are in the database". When you don't specify ordering on a column, like country, the database is free to return it however it wants, and inconsistently between queries. If ordering matters, then you should order the columns.

[Ireneusz Kordal]

Does clause 'order by type, country' not work ?

select type, country from test_pnl_report
order by type, country 

'group by' clause is used with conjunction with aggregate functions,
if you try execute this query you will get SQL error:

  select type, country from test_pnl_report
group by type
order by type  

[Goran Stankovic]

Scott Selikoff wrote:What exactly is the problem? There's no such ordering as "as they are in the database". When you don't specify ordering on a column, like country, the database is free to return it however it wants, and inconsistently between queries. If ordering matters, then you should order the columns.

For example,

type country
----------- --------------
Currency United States
Bond Future Japan
Bank Debt United States
Bond Future United States
Bond Future United States
Bond Future New Zealand
Bond Future Canada
Bond Future United States
Currency Japan

select type, country from test_pnl_report  
group by type

returns

type country
----------- --------------
Currency United States
Currency Japan
Bond Future Japan
Bond Future United States
Bond Future United States
Bond Future New Zealand
Bond Future Canada
Bond Future United States
Bank Debt United States

I just want to sort by type now. That is all.

type country
----------- --------------
Bank Debt United States
Bond Future Japan
Bond Future United States
Bond Future United States
Bond Future New Zealand
Bond Future Canada
Bond Future United States
Currency United States
Currency Japan

In the group for Currency I want United States to be first. And I don't want country to by sorted (neither ASC nor DESC).

[Jan Cumps]

Hi Goran,

If you don't order by a field, you cannot predict how it will be returned.

If you order by country, it will return country in alphabetic order.
If you don't order by country, you have no control over how the database returns your values.

In the group for Currency I want United States to be first. And I don't want country to by sorted

In the sql language, you cannot set expectations if you do not tell the database how to order. The database will do as it pleases.

Regards, Jan

[Goran Stankovic]

Thanks. I will do just "group by" and some sort on the client side part of the application.

[Jan Cumps]

Hi Goran,

if it is just for sorting, I would advise you to use ORDER BY in stead of GROUP BY.
ORDER BY is the dedicated clause to perform sorting.
GROUP BY has another purpose: to group results by certain criteria, so that you can perform aggregations.

From W3Schools.

Regards, Jan