-
Jan 16th, 2023, 05:33 AM
#1
[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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 16th, 2023, 05:49 AM
#2
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 16th, 2023, 05:52 AM
#3
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
-
Jan 16th, 2023, 05:53 AM
#4
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jan 16th, 2023, 07:34 AM
#5
Re: mssql Orderby email and date together
Originally Posted by sapator
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
-
Jan 16th, 2023, 08:53 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|