*
The moose likes JDBC and the fly likes SQL help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL help" Watch "SQL help" New topic
Author

SQL help

Goran Stankovic
Greenhorn

Joined: Feb 16, 2010
Posts: 4
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
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

Joined: Feb 16, 2010
Posts: 4
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).

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Goran Stankovic
Greenhorn

Joined: Feb 16, 2010
Posts: 4
Thanks. I will do just "group by" and some sort on the client side part of the application.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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 agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL help
 
Similar Threads
reach of Javaranch
Which country to immigrate to..?
free retake on sun vouchers
What countries have you traveled? Which one do you like the most?
[political] Clear me on WMD arithmetics