Select row with the most recent date per user

Hi Community,

I would like to get the most recent update by created date per user in my db. Not sure how I can do it in my subquery.


Looking for some help.

Solution

Hello Alvin,

As far as I understood, you want to pick the most recent date for CreatedDate column in your subquery.

I don't know your datamodel neither your data, but I'm assuming that you have an User column in your UserSMSRespond entity (from what you described above).

Maybe, you can try to implement your subquery as:

SELECT {UserSMSRespond}.[User], MAX({UserSMSRespond}.[CreatedDate])
FROM {UserSMSRespond}
GROUP BY {UserSMSRespond}.User

Basically, this will group by user and get the MAX value for created date.


You could change this User column by your MobileNumber column, if the mobile number is unique per user.


Please be aware that if you are using a join clause with a subquery, you need to have an alias for that subquery.

Also, if you are going to use a specific column on your join, you need to select it in the subquery as well.

And one last thing, your subquery should be between parentheses ( ) and not braces { }.


Hope that this helps you! :)


Kind regards,

Rui Barradas

Solution

Hi Alvin,

   You can try this way.

 

Regards,

Zhou Shuai

Zhou Shuai wrote:

Hi Alvin,

   You can try this way.

 

Regards,

Zhou Shuai

 Hi Zhou Shuai

I want to get the latest created date first before I do the ticket.contact no.

What should I edit as it is showing error now?


 

Hey Alvin,

You can also try to do your join like this:


LEFT JOIN {UserSMSRespond} ON {UserSMSRespond}.[Id] =

    SELECT TOP 1 {UserSMSRespond}.[Id] 

         FROM {UserSMSRespond}

                WHERE  {UserSMSRespond}.[MobileNumber] = {Ticket}.[ContactNo] 

                ORDER BY {UserSMSRespond}.[CreatedDate] DESC 

)


And on your Select you can use {UserSMSRespond}.[Message] Normally for example.


Other suggestion... you really should be getting FROM Notification? Or you could use FROM Ticket and then Joining with Notification?


Seems to me that your main entity is Ticket and not Notification.

If your Ticket has more than one Notification you will get duplicate values. You could do the same thing you did with UsersSMS to bring just one Notification also.


Cheers and Regards,


RR :)

Raphael Ranieri wrote:

Hey Alvin,

You can also try to do your join like this:


LEFT JOIN {UserSMSRespond} ON {UserSMSRespond}.[Id] =

    SELECT TOP 1 {UserSMSRespond}.[Id] 

         FROM {UserSMSRespond}

                WHERE  {UserSMSRespond}.[MobileNumber] = {Ticket}.[ContactNo] 

                ORDER BY {UserSMSRespond}.[CreatedDate] DESC 

)


And on your Select you can use {UserSMSRespond}.[Message] Normally for example.


Other suggestion... you really should be getting FROM Notification? Or you could use FROM Ticket and then Joining with Notification?


Seems to me that your main entity is Ticket and not Notification.

If your Ticket has more than one Notification you will get duplicate values. You could do the same thing you did with UsersSMS to bring just one Notification also.


Cheers and Regards,


RR :)

 

 Hi RR,


can you enlighten me on this: 

I don't really understand what is going on here haha (Still new to sql)

Alvin Teo wrote:

Zhou Shuai wrote:

Hi Alvin,

   You can try this way.

 

Regards,

Zhou Shuai

 Hi Zhou Shuai

I want to get the latest created date first before I do the ticket.contact no.

What should I edit as it is showing error now?


 

 

 Hi Alvin,


   what error did you get? 


Regards,

Zhou Shuai

Alvin Teo wrote:

Zhou Shuai wrote:

Hi Alvin,

   You can try this way.

 

Regards,

Zhou Shuai

 Hi Zhou Shuai

I want to get the latest created date first before I do the ticket.contact no.

What should I edit as it is showing error now?


 

 

 Hi Alvin,

    The error you got is because you reversed below two parts.

Regards,

Zhou Shuai

Alvin Teo wrote:

Raphael Ranieri wrote:

Hey Alvin,

You can also try to do your join like this:


LEFT JOIN {UserSMSRespond} ON {UserSMSRespond}.[Id] =

    SELECT TOP 1 {UserSMSRespond}.[Id] 

         FROM {UserSMSRespond}

                WHERE  {UserSMSRespond}.[MobileNumber] = {Ticket}.[ContactNo] 

                ORDER BY {UserSMSRespond}.[CreatedDate] DESC 

)


And on your Select you can use {UserSMSRespond}.[Message] Normally for example.


Other suggestion... you really should be getting FROM Notification? Or you could use FROM Ticket and then Joining with Notification?


Seems to me that your main entity is Ticket and not Notification.

If your Ticket has more than one Notification you will get duplicate values. You could do the same thing you did with UsersSMS to bring just one Notification also.


Cheers and Regards,


RR :)

 

 Hi RR,


can you enlighten me on this: 

I don't really understand what is going on here haha (Still new to sql)

 Hi alvin,


I'm sorry for the late reply...
What I did there was to JOIN your UserSMS entity with a sub query to get only the first row, based in your create date.


There are many ways of doing it, some are a little bit more performance wise than others.

It will depends on your data model and indexes.

I suggest you to see this article about 4 Ways to Join Only The First Row in SQL.

It will explain some tricks to have the best performance in a case like yours.


Cheers and Regards,

RR :)