hi everyone,
i got one sql query questions to ask you experts, i have attached a txt file with a set of sql tables,maybe u can take a look
i have this JOINED table
----------------
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C65 656 shihui 2003-03-26 09:23 AM
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C8 300 henry 2003-01-07 09:12 AM
i used this sql query to execute to get the query set above
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY PM_REFNO ASC
I would like to know how to my sql query can execute in such a way that
i would like to arrange C10,C9 with the PM_REFNO where PM_DATEUPDATED IS NULL and then follow by PM_DATEUPDATED latest date. I would like my query results to be
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
can i know how to build this querysomthing like this?
-- Set Option Value
-- -------- ----
-- textsize 64512
-- language us_english
-- dateformat mdy
-- datefirst 7
-- arithabort SET
-- nocount SET
-- remote_proc_transactions SET
-- ansi_null_dflt_on SET
-- disable_def_cnst_chk SET
-- ansi_warnings SET
-- ansi_padding SET
-- ansi_nulls SET
-- concat_null_yields_null SET
create table #Tmp(PM_REEFNO varchar(4), PM_ID int, PSUB_NAME varchar(15), PM_DATEUPDATED datetime)
go
insert into #Tmp values('C23',100,'karen','2003-03-24 11:38 AM')
insert into #Tmp values('C11',567,'chris','2003-03-11 10:45 PM')
insert into #Tmp values('C65',656,'shihui','2003-03-26 09:23 AM')
insert into #Tmp values('C10',546,'yumei',NULL)
insert into #Tmp values('C9',209,'sinlan',NULL)
insert into #Tmp values('C8',300,'henry','2003-01-07 09:12 AM')
go
select *
From #Tmp
order by case when PM_DATEUPDATED is null then '31-Dec-9999' else PM_DATEUPDATED end desc
, PM_REEFNO
go
drop table #Tmp
go|||hi everyone,
the above the user dexmix is correct using this sql statement
i tried to use this query he gives me
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY IsNull(PM_DATEUPDATED,NULL), PM_DATEUPDATED DESC , PM_REFNO
the results are
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
C2 209 sinlan 2003-01-06 09:13 AM
C1 546 yumei 2003-01-01 07:15 PM
how can i build another query that i would like the table to appear like this
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
and i tried to change the query to
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC
but it does not work and the results display is not i want?
Can anyone please help?|||sorry i made some changes
this is my sql statement
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC
i get this results
Table 1
---
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C2 209 sinlan 2003-01-06 09:13 AM
C8 300 henry 2003-01-07 09:12 AM
C11 567 chris 2003-03-11 10:45 PM
C23 100 karen 2003-03-24 11:38 AM
C65 656 shihui 2003-03-26 09:23 AM
how to i build this query?
Table 2
----
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
that means in my first post i want the order to be first order by PM_DateUPDATED IS NULL reflected in Table 2, Then By the PM_ID so that they can group together which is easier to view, then follow by PM_REFNO in DESC order .As u can see from Table 2 that C10 the date is null and the follow by a early date.However the problem lies with the in Table 1 cos the query forces ISNULL(PM_DATEUPDATED,NULL) to arrange in ASC|||Hi
Try this -
select *
From Tmp
order by
PSUB_NAME desc, case when PM_DATEUPDATED is null then '31-Dec-9999' else PM_DATEUPDATED end desc
I think it will work...
Cheers
Gola
Originally posted by verybrightstar
sorry i made some changes
this is my sql statement
SELECT * FROM PAYMENT_INFO , PROFILE_SUBSCRIBER WHERE PM_REFNO LIKE 'C%' AND PM_ID = PSUB_ID ORDER BY ISNULL(PM_DATEUPDATED,NULL) , PM_MSISDN ASC
i get this results
Table 1
---
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C9 209 sinlan <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C2 209 sinlan 2003-01-06 09:13 AM
C8 300 henry 2003-01-07 09:12 AM
C11 567 chris 2003-03-11 10:45 PM
C23 100 karen 2003-03-24 11:38 AM
C65 656 shihui 2003-03-26 09:23 AM
how to i build this query?
Table 2
----
PM_REEFNO PM_ID PSUB_NAME PM_DATEUPDATED
----------------
C10 546 yumei <NULL>
C1 546 yumei 2003-01-01 07:15 PM
C9 209 sinlan <NULL>
C2 209 sinlan 2003-01-06 09:13 AM
C65 656 shihui 2003-03-26 09:23 AM
C23 100 karen 2003-03-24 11:38 AM
C11 567 chris 2003-03-11 10:45 PM
C8 300 henry 2003-01-07 09:12 AM
that means in my first post i want the order to be first order by PM_DateUPDATED IS NULL reflected in Table 2, Then By the PM_ID so that they can group together which is easier to view, then follow by PM_REFNO in DESC order .As u can see from Table 2 that C10 the date is null and the follow by a early date.However the problem lies with the in Table 1 cos the query forces ISNULL(PM_DATEUPDATED,NULL) to arrange in ASC
