• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL help

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Does clause 'order by type, country' not work ?


'group by' clause is used with conjunction with aggregate functions,
if you try execute this query you will get SQL error:
 
Goran Stankovic
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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



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).

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks. I will do just "group by" and some sort on the client side part of the application.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
I am not a spy. Definitely. Definitely not a spy. Not me. No way. But this tiny ad ...
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic