Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    7

    Question Unanswered: asking help for sql query

    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 query
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    somthing like this?

    Code:
    -- 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
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    7

    Re: asking help for sql query

    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?

  4. #4
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    7

    Talking Re: asking help for sql query

    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

  5. #5
    Join Date
    Apr 2003
    Posts
    18

    Re: asking help for sql query

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •