How to retrieve the last paid values in Table in sql db

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

paid_table

id        name       Paid_amt          total_paid_amt      Paid_Date
2         Anup         50                          50       1.4.2015
3         Anil        100                         100       1.4.2015
2         Anup         50                         100        2.42015
4        Kumar          0                           0              0
5       Naveen          0                           0              0
4        Kumar        100                         100       4.4.2015

Answer will like this

id        name       Paid_amt          total_paid_amt      Paid_Date
2         Anup         50                         100        2.42015  
3         Anil        100                         100       1.4.2015
4        Kumar        100                         100       4.4.2015
5       Naveen          0                           0              0

How to write the query..........

[edit]Code block added - OriginalGriff[/edit]

Posted 4-May-15 19:08pm

Member 11644272

Updated 4-May-15 19:29pm

OriginalGriff

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-05-04T19:40:00

The first thing to do is to look at your data: and it's got problems.
It looks like you are storing dates in a string format, or you couldn't get values for Paid_date like "2.42015" and "0" - neither of which are valid for DATETIME fields. And string based dates are a pain to work with - easy to insert, if you are lazy, but a nightmare to use every single time later.

So change your database, and use appropriate data type for each column. I'd also recommend creating a "users" table to hold the user names ("Anup", "Naveen" and so forth) and only store the ID reference in your main table. That way, you can have two people with similar or identical names and it won't mess up your data - and you don't waste space storing duplicate information. SQL is very good at relating such information!

Then it's a relatively simple bit of SQL to get your desired rows, JOIN with a GROUP subquery will do it easily:

SQL

SELECT t.*
FROM Paid_Table t
JOIN (
  SELECT name, MAX(Paid_date) AS Paid_Date
  FROM Paid_Table
  GROUP BY name) AS t2
  ON t.name = t2.name AND t.Paid_Date = t2.Paid_Date

But with strings? Horribly complex, I'm afraid.