Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help (oracle 11g)
SQL query help [message #638232] Sun, 07 June 2015 20:17 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member

Hello all,

How you doing.

I have view written (VIEW_TPL_RQST) upon querying this view with id it will return records in order M,I,N which is expected.

SELECT  VIEW_TPL_RQST.PAYLOAD FROM C_MHE_VIEW_PRICE_TKT_RQST where id='1234'



but when i add DISTINCT below query it returned records in I,M,N ( order is not consistent)

SELECT DISTINCT VIEW_TPL_RQST.PAYLOAD FROM C_MHE_VIEW_PRICE_TKT_RQST where id='1234'



I need your help even if distinct applied to above query it should return values in M,I,N how can i do that.

Thanks
Re: SQL query help [message #638235 is a reply to message #638232] Sun, 07 June 2015 20:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
if you want order, then you must use ORDER BY clause
using ORDER BY ON A view, is silly, foolish & wasteful since rows in a table or view have no inherent order.
Re: SQL query help [message #638236 is a reply to message #638235] Mon, 08 June 2015 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, your queries are invalid.

[Updated on: Mon, 08 June 2015 00:14]

Report message to a moderator

Re: SQL query help [message #638237 is a reply to message #638232] Mon, 08 June 2015 00:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
gorants wrote on Mon, 08 June 2015 06:47
it will return records in order M,I,N which is expected.


No, there is no guarantee that rows are always returned in the same order without an explicit ORDER BY clause.

To have a custom order, you could use a CASE expression. For example,

SQL> WITH DATA(id) AS(
  2  SELECT 'X' FROM dual UNION ALL
  3  SELECT 'A' FROM dual UNION ALL
  4  SELECT 'Z' FROM dual UNION ALL
  5  SELECT 'M' FROM dual UNION ALL
  6  SELECT 'I' FROM dual UNION ALL
  7  SELECT 'N' FROM dual
  8  )
  9  SELECT * FROM DATA
 10  ORDER BY
 11  CASE id
 12  WHEN 'M' THEN
 13    1
 14  WHEN 'I' THEN
 15    2
 16  WHEN 'N' THEN
 17    3
 18  ELSE
 19    4
 20  END, id
 21  /

I
-
M
I
N
A
X
Z

6 rows selected.

SQL>


Keep in mind that 'M' and 'm' are not same, so if your data is case insensitive, then you need to handle that in the CASE expression with an OR condition. Else, characters will be sorted based on their ASCII values.

The ORDER BY should be in the underlying query of the view.


Regards,
Lalit

[Updated on: Mon, 08 June 2015 00:31]

Report message to a moderator

Re: SQL query help [message #638238 is a reply to message #638237] Mon, 08 June 2015 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
The ORDER BY should be in the underlying query of the view.


Wrong, no view should have an ORDER BY clause.

Re: SQL query help [message #638239 is a reply to message #638238] Mon, 08 June 2015 01:40 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
That's stupid of me, I didn't phrase it properly. I mean, if OP wants a particular order, then use ORDER BY in the query. What I should have added is, if OP wants ordered set of rows while fetching from the view, then the ORDER BY is needed in that query too. SELECT .. FROM view .. ORDER BY CASE..

I agree, ORDER BY makes no sense in the underlying query of the view, as it is just like fetching from base table.

[Updated on: Mon, 08 June 2015 01:42]

Report message to a moderator

Previous Topic: Date range from table
Next Topic: ORA-02437: cannot validate (TAGICRM.PK_RISK_GRID) - primary key violated
Goto Forum:
  


Current Time: Tue Mar 19 01:57:03 CDT 2024