|
View previous topic :: View next topic
|
| Author |
Message |
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
| Yes ,so Group by on first column should work then. |
If he wants to select all Columns then he will need to group by on all columns which I feel will be the overhead.
CASE function will also work same as COALESCE.
@Mistah..is it possible for you to provide queries and output for both using ORDER BY DATE_COLUMN and ORDER BY 2
Thanks,
Chandan |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Let us not guess how many columns he has in select.
However , this should work.
| Code: |
SELECT Key_Column, COALESCE(Date_Column,'0001-01-01') mod_dt
FROM Date_table
WHERE Key_Column = 'AA'
ORDER BY [i]mod_dt[/i] ASC
FETCH FIRST ROW ONLY |
| Quote: |
@Mistah..is it possible for you to provide queries and output for both using ORDER BY DATE_COLUMN and ORDER BY 2 |
Results would be different, Null is lower which comes first . |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| Rohit Umarjikar wrote: |
Results would be different, Null is lower which comes first . |
I guess you have missed below post from Enrico
| Quote: |
| Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order. |
Also the solution is provided by you is the same as per provided by me and both will work, but as per Mistah it's not working for him
Anyways this post is going way longer, let wait for Mistah to get back
Thanks and regards,
Chandan |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Hi Chandan
Sorry for the delay and confusion. I have restricted access to internet, so could not reply earlier. ORDER BY 2 is working just as I needed.
So as I understand the deifference is:
ORDER BY 2 ASC - ordering is done after the values are updated by COALESCE function
ORDER BY Date_Column ASC - ordering is done before COALESCE function is applied.
However if I don't use the COALESCE function, resulting rows will be same in both the cases.
Thanks a lot. It was new to me. |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
| No worries. Glad to know its working for you and I could help |
|
| Back to top |
|
|
|
|
