Max date per username

Question

0.00/5 (No votes)

See more:

Hi there,

I am trying to get the lastest date per username. The data looks as follows:

Date Username Amount PID
12-12-2015 Mr Y R50 5997897
15-12-2015 Mr Y R56 8784545
13-07-2015 Ms J R89 87824
04-04-2015 Ms J R87 875414

So the output needs to be:
Date Username Amount PID
15-12-2015 Mr Y R56 8784545
13-07-2015 Ms J R89 87824

I have tried the following to no success:

SQL

select s1.Date, s1.UserName, s2.TradeAmount  from #ListOfTrans s1 
inner join
(
  select max(a.Date) as  Date,
    a.Amount, a.Username, a.PortfolioID
  from  #ListOfTrans a
  group by UserName,Amount,Date, PID
) s2
  on s1.UserName = s2.UserName
  and s1.Date = s2.Date
  and s1.PID = s2.PID
  group by s1.UserName, s2.Amount,s1.Date

Posted 14-Jan-16 23:24pm

Miss R

Add a Solution

Comments

Wombaticus 15-Jan-16 5:30am

AM I missing something? WHat's wrong with
select Date, UserName, TradeAmount from ListOfTrans order by Date desc limit 2
?

Wombaticus 15-Jan-16 6:54am

OK, sorry - me not reading the question properly... :(

3 solutions

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)

_Asif_ · Answer 1 · 2016-01-14T23:35:00

Solution 1

Change your query like this and try again

SQL

select T.*
from    
(
  select a.UserName, max(a.Date) as  MAXDate
  from  #ListOfTrans a
  group by UserName
) T INNER JOIN #ListOfTrans LT ON T.UserName = LT.UserName AND T.Date = LT.MAXDATE

Posted 14-Jan-16 23:35pm

_Asif_

Comments

Maciej Los 15-Jan-16 15:12pm

Looks perfect, +5!

snorkie · Answer 2 · 2016-01-15T03:25:00

SQL is great, cause there are tons of ways to write something. The one above will work, but I prefer the syntax below.

C#

SELECT (SELECT MAX(Date)
		FROM #ListOfTrans T2
		WHERE T2.UserName = T1.UserName) Date,
		UserName,
		Amount, 
		PID
FROM #ListOfTrans T1

One thing to note here, if this table is going to be large, this query could get slow. You should consider adding an index on the table of UserName and Date column.

Hogan

Peter Leow · Answer 3 · 2016-01-15T04:52:00

First thing first, use proper data type for the intended purpose of each table column, e.g. Date[^] instead of varchar for the date field, that will eliminate any data error and ugly conversion code.
Secondly, avoid using keywords or data type name such as 'date' as field name.
Then, try this:

C#

select * from tablename t1 where datefield = (
select max(datefield) from tablename t2 where t1.username=t2.username)

Max date per username

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0