22
Views
8
Comments
Solved
Select row with the most recent date per user
Question

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.

Rank: #86
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

Rank: #1056

Hi Alvin,

   You can try this way.

 

Regards,

Zhou Shuai

Rank: #5363

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?


 

Rank: #166

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 :)

Rank: #5363

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)