View previous topic :: View next topic
|
Author |
Message |
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
There is a table, let's say for sake of simplicity,Date_Table with two columns: Key_Column and a Date_column. My requirement:
To fetch that row where the date is NULL or else with oldest date.
Sample data:
Code: |
AA 2014-01-06
AA 1984-01-06
AA ---------- |
I tried with the query:
Code: |
SELECT Key_Column, COALESCE(Date_Column,'0001-01-01')
FROM Date_table
WHERE Key_Column = 'AA'
ORDER BY Date_Column ASC
FETCH FIRST ROW ONLY |
But it's not returning the required row.
Expected row:
Is there any way I can get the desired row without using cursor. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
"AND date_column = NULL" ?
If you want the null value row then you have to specify it just like you specified AA for the row key - duh! |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi,
Change the order by as below
Thanks,
Chandan |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Nicc,
With due respect should it be = NULL or IS NULL? |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Dunno - just doing it off the top of my head without looking at any manual (hence the '?' at the end!). |
|
Back to top |
|
|
DavidMig
New User
Joined: 22 Jan 2016 Posts: 1 Location: Spain
|
|
|
|
Should be IS NULL. |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
I'm sorry for not being clear. I'm getting the key from a file and have to fetch the date from the table. What I meant was I have to fetch the oldest date for a key. But in case if for a key a null value is present with other values then I want fetch the row with null.
For ex..
if the data is as below
Code: |
AA 2014-01-06
AA 1984-01-06
AA 1957-01-06
|
then expected row to be returned is
And if data is as below
Code: |
AA 2014-01-06
AA 1984-01-06
AA ---------- |
then expected row to be returned is
So I can't use IS NULL.
Hope I'm clear now. Please let me know if you have any follow up questions. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
Use 2 queries. First one with Where condition as 'DATE IS NULL'. If you get SQL code as 100 then second query with Order by Date asc (fetch first row only) or you can use MIN function.
. |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Thanks Rahul. This is really a good idea rather than using a cursor and running a perform loop. Thanks a lot. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Mistath,
I am not clear why the earlier solution not working for you?
If you use Order by 2 like below instead of ORDER BY DATE_COLUMN it should work. This is what I have suggested in my earlier post.
Code: |
SELECT Key_Column, COALESCE(Date_Column,'0001-01-01')
FROM Date_table
WHERE Key_Column = 'AA'
ORDER BY 2 ASC
FETCH FIRST ROW ONLY |
Let me know if I am missing anything here. Just curious to know do we really need two queries here?
Also what if there are two rows with NULL value?
Thanks,
Chandan |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Quote: |
Code: |
Order by 2 like below instead of ORDER BY DATE_COLUMN |
|
I don't understand why do you think this will behave differently.
When there are null value present, the query ignore the null and returns the row with oldest value.
Quote: |
Also what if there are two rows with NULL value? |
That's fine. As they are identical, they won't have nay impact on further processing. Same in case of row with two valid and equal dates. |
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
mistah kurtz wrote: |
I don't understand why do you think this will behave differently.
|
have you tried to execute the query with giving ORDER BY 2? Are you getting the same results?
it will work differently because ORDER BY DATE_COLUMN will apply the order by clause on value of DATE_COLUMN and it will still have NULL even you are giving COALESCE in query
Where as with ORDER BY 2 order by clause will be applicable to value returning as result of COALESCE function which will '0001-01-01' for a null column |
|
Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012 Posts: 316 Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
Quote: |
have you tried to execute the query with giving ORDER BY 2? |
Yes. I'm getting the same results? |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
the order by clause does not give the expected result because You ( all ) did not read the manual
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. |
|
|
Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi Enrico,
with due respect, I am aware about above from manual.
I have posted above solution after testing it out in SPUFI and its working for me
Also I had worked on similar queries and those did work for me
I am just wondering why its not working e results for Mistah..
Regards,
Chandan |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Quote: |
The null value is higher than all other values |
therefore the sequence is 1,2,3,NULL so returning first row returns 1 not NULL. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
Agree with Chandan. There is a difference between having Order by with 'Date_Column' and having it as Order by '2'.
Mistah, I think you haven't tried what Chandan had suggested. Run it and you'll get your desired result.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Why not use a CASE WHEN NULL then low date else min(date)? |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
Rohit Umarjikar wrote: |
Why not use a CASE WHEN NULL then low date else min(date)? |
Because he doesn't want the date.
He wants the row (all column values) when the date is null / min value when not null, through a single select query.
. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Yes ,so Group by on first column should work then. |
|
Back to top |
|
|
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: 3048 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 |
|
|
|