SQL query help [message #638232] |
Sun, 07 June 2015 20:17 |
|
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 #638237 is a reply to message #638232] |
Mon, 08 June 2015 00:19 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
gorants wrote on Mon, 08 June 2015 06:47it 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 #638239 is a reply to message #638238] |
Mon, 08 June 2015 01:40 |
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
|
|
|