Results 1 to 6 of 6

Thread: [RESOLVED] mssql Orderby email and date together

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Resolved [RESOLVED] mssql Orderby email and date together

    Hi.
    I have emails and dates.
    Some emails have more rows with different dates.
    So I can order fine by email as this will give:

    email1,2022-01-01
    email1,2023-01-01
    email4,2022-01-01

    but if I order by date first it will give

    email1,2022-01-01
    email4,2022-01-01
    email1,2023-01-01

    What i need to do is group the mails together when I order by date first
    So it will give
    2022-01-01,email1
    2023-01-01,email1
    2022-01-01,email4

    Even if the order is by date and then email.
    I was thinking a case with a sum or a partition by but I am at loss and I'm not sure how I can search it online, what exactly to write to the search engine.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: mssql Orderby email and date together

    This to be more clear on what I try

    Code:
      With CTE (Email,ExpirationDate) AS (select distinct email,ExpirationDate from Registration where PaidDate is not null and CardNumber is not null and ExpirationDate >= getdate()
     and Email <> 'DELETED' )
    
      select EX.email,EX.ExpirationDate from Registration EX
      INNER JOIN (SELECT email, MAX(ExpirationDate) MaxDate
                FROM Registration
                GROUP BY email) B on EX.Email = B.Email
       inner join CTE C on EX.Email = C.Email
      Where EX.PaidDate is not null and EX.CardNumber is not null  
    
      order by B.MaxDate,Ex.ExpirationDate,EX.email
    So I first trying to emails with >= ExpirationDate and from that email I try to get the complete data (even lower dates) and group per email and expiration.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,414

    Re: mssql Orderby email and date together

    it's still "... ORDER BY MailAddress, MyDate"
    The order how you represent the Fields is in the SELECT

    Code:
    SELECT MyDate, MailAddress, OtherStuff FROM SomeTable ORDER BY MailAddress, MyDate
    ....or i've misunderstood your problem completely
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: mssql Orderby email and date together

    OK.
    Lol I thing I have fixed it and posted the solution (order by B.MaxDate).
    It is correct right?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,414

    Re: mssql Orderby email and date together

    Quote Originally Posted by sapator View Post
    OK.
    Lol I thing I have fixed it and posted the solution (order by B.MaxDate).
    It is correct right?
    No, you still sort by Date first
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: [RESOLVED] mssql Orderby email and date together

    Yes I want to sort by date as I've said in the OP. If I was sorting by email then I wouldn't have had any issue in the first place.
    Running the query works as expected. Dates and if email has 2 dates it shows the previous date of the email person and continues with the sort.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

Posting Permissions

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



Click Here to Expand Forum to Full Width