|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Order problemvalid_until proposal_date 25.10.2005 NULL 23.10.2005 26.10.2005 NULL 20.10.2005 28.10.2005 24.10.2005 22.10.2005 28.10.2005 how to select that result will be NULL 20.10.2005 22.10.2005 28.10.2005 23.10.2005 26.10.2005 28.10.2005 24.10.2005 25.10.2005 NULL Kuido -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 Add a Case statement to the Order by Clause:
Order by ... Case When valid_until is Null Then 0 Else 1 End, valid_until, ... "Kuido Külm via SQLMonster.com" <u2904@uwe> wrote in message news:56d33b2038041@uwe...Show quote >I have table > > valid_until proposal_date > 25.10.2005 NULL > 23.10.2005 26.10.2005 > NULL 20.10.2005 > 28.10.2005 24.10.2005 > 22.10.2005 28.10.2005 > > how to select that result will be > NULL 20.10.2005 > 22.10.2005 28.10.2005 > 23.10.2005 26.10.2005 > 28.10.2005 24.10.2005 > 25.10.2005 NULL > > Kuido > > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 Kuido Külm via SQLMonster.com wrote:
Show quote > I have table CASE WHEN proposal_date IS NULL THEN 1 ELSE 0 END,> > valid_until proposal_date > 25.10.2005 NULL > 23.10.2005 26.10.2005 > NULL 20.10.2005 > 28.10.2005 24.10.2005 > 22.10.2005 28.10.2005 > > how to select that result will be > NULL 20.10.2005 > 22.10.2005 28.10.2005 > 23.10.2005 26.10.2005 > 28.10.2005 24.10.2005 > 25.10.2005 NULL > > Kuido > > ORDER BY valid_until HTH, Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Thank you.
Kuido Bob Barrows [MVP] wrote: Show quote >> I have table >> >[quoted text clipped - 13 lines] >> >> Kuido > >ORDER BY > CASE WHEN proposal_date IS NULL THEN 1 ELSE 0 END, > valid_until > >HTH, >Bob Barrows > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 If the source table looks like:
valid_until proposal_date ------------------------------------------------------ ----------------------- ------------------------------- 2005-10-25 00:00:00.000 NULL 2005-10-23 00:00:00.000 2005-10-26 00:00:00. 000 NULL 2005- 10-20 00:00:00.000 2005-10-28 00:00:00.000 2005-10-24 00:00:00. 000 2005-10-22 00:00:00.000 2005-10-28 00:00:00. 000 2005-10-27 00:00:00.000 NULL 2005-10-27 00:00:00.000 NULL 2005-10-27 00:00:00.000 2005-10-24 00:00:00. 000 2005-10-27 00:00:00.000 2005-10-26 00:00:00. 000 then the result is not correct valid_until proposal_date ------------------------------------------------------ ----------------------- ------------------------------- NULL 2005- 10-20 00:00:00.000 2005-10-22 00:00:00.000 2005-10-28 00:00:00. 000 2005-10-23 00:00:00.000 2005-10-26 00:00:00. 000 2005-10-27 00:00:00.000 2005-10-24 00:00:00. 000 2005-10-27 00:00:00.000 2005-10-26 00:00:00. 000 2005-10-28 00:00:00.000 2005-10-24 00:00:00. 000 2005-10-25 00:00:00.000 NULL 2005-10-27 00:00:00.000 NULL 2005-10-27 00:00:00.000 NULL the row nr. 4 (2005-10-27 00:00:00.000 2005-10- 26 00:00:00.000) must be under row nr 6. (2005-10-25 00:00:00.000 NULL) Kuido Bob Barrows [MVP] wrote: Show quote >> I have table >> >[quoted text clipped - 13 lines] >> >> Kuido > >ORDER BY > CASE WHEN proposal_date IS NULL THEN 1 ELSE 0 END, > valid_until > >HTH, >Bob Barrows > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 Why? I thought you wanted the rows with null proposal_date to be last ...
You will need to do a better job explaining this sort order. Kuido Külm via SQLMonster.com wrote: Show quote > If the source table looks like: > > > valid_until proposal_date > > ------------------------------------------------------ > ----------------------- > ------------------------------- > 2005-10-25 00:00:00.000 NULL > 2005-10-23 00:00:00.000 2005-10-26 > 00:00:00. 000 > NULL > 2005- 10-20 00:00:00.000 > 2005-10-28 00:00:00.000 2005-10-24 > 00:00:00. 000 > 2005-10-22 00:00:00.000 2005-10-28 > 00:00:00. 000 > 2005-10-27 00:00:00.000 NULL > 2005-10-27 00:00:00.000 NULL > 2005-10-27 00:00:00.000 2005-10-24 > 00:00:00. 000 > 2005-10-27 00:00:00.000 2005-10-26 > 00:00:00. 000 > > > then the result is not correct > valid_until proposal_date > > ------------------------------------------------------ > ----------------------- > ------------------------------- > NULL > 2005- 10-20 00:00:00.000 > 2005-10-22 00:00:00.000 2005-10-28 > 00:00:00. 000 > 2005-10-23 00:00:00.000 2005-10-26 > 00:00:00. 000 > 2005-10-27 00:00:00.000 2005-10-24 > 00:00:00. 000 > 2005-10-27 00:00:00.000 2005-10-26 > 00:00:00. 000 > 2005-10-28 00:00:00.000 2005-10-24 > 00:00:00. 000 > 2005-10-25 00:00:00.000 NULL > 2005-10-27 00:00:00.000 NULL > 2005-10-27 00:00:00.000 NULL > > the row nr. 4 (2005-10-27 00:00:00.000 > 2005-10- 26 00:00:00.000) must > be under row nr 6. (2005-10-25 00:00:00.000 > NULL) > > Kuido > > > > > Bob Barrows [MVP] wrote: >>> I have table >>> >> [quoted text clipped - 13 lines] >>> >>> Kuido >> >> ORDER BY >> CASE WHEN proposal_date IS NULL THEN 1 ELSE 0 END, >> valid_until >> >> HTH, >> Bob Barrows -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" SELECT valid_until, proposal_date
FROM your_table ORDER BY COALESCE(valid_until, proposal_date) ; -- David Portas SQL Server MVP -- The result is not in correct order, to rows nr. 4 and 5 order must be changed
Kuido valid_until proposal_date ------------------------------------------------------ ----------------------- ------------------------------- NULL 2005- 10-20 00:00:00.000 2005-10-22 00:00:00.000 2005-10-28 00:00:00. 000 2005-10-23 00:00:00.000 2005-10-26 00:00:00. 000 2005-10-25 00:00:00.000 NULL 2005-10-28 00:00:00.000 2005-10-24 00:00:00. 000 David Portas wrote: >SELECT valid_until, proposal_date > FROM your_table > ORDER BY COALESCE(valid_until, proposal_date) ; > -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 So, if the source table is:
valid_until proposal_date ----------- ------------- 25.10.2005 NULL 23.10.2005 26.10.2005 NULL 20.10.2005 28.10.2005 24.10.2005 22.10.2005 28.10.2005 27.10.2005 NULL 27.10.2005 NULL 27.10.2005 24.10.2005 27.10.2005 26.10.2005 NULL 26.10.2005 23.10.2005 NULL then the result must be; valid_until proposal_date ----------- ------------- NULL 20.10.2005 22.10.2005 28.10.2005 23.10.2005 26.10.2005 23.10.2005 NULL 27.10.2005 24.10.2005 28.10.2005 24.10.2005 25.10.2005 NULL 27.10.2005 26.10.2005 NULL 26.10.2005 27.10.2005 NULL 27.10.2005 NULL so if the proposal_date < valid_until then proposal_date else valid_until Kuido -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 One strange possible solution:
order by coalesce(case when proposal_Date < valid_until then proposal_date else valid_until end, case when proposal_Date > valid_until then valid_until else proposal_Date end) -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200511/1 On Fri, 04 Nov 2005 07:55:31 GMT, Kuido Külm via SQLMonster.com wrote:
>One strange possible solution: Hi Kuido,> >order by coalesce(case > when proposal_Date < valid_until then proposal_date > else valid_until >end, >case > when proposal_Date > valid_until then valid_until > else proposal_Date >end) That's indeed the solution you need. Except that you got the < sign reversed in the second CASE expression. It should read < in both. order by coalesce(case when proposal_Date < valid_until then proposal_date else valid_until end, case when proposal_Date < valid_until then valid_until else proposal_Date end) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics