Max(Date) Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max(Date) Question

Author

Topic

dfeteau
Starting Member

4 Posts

Posted - 2011-09-06 : 18:57:07

I've seen this question asked all over the net but can't find a clear answer. I have two tables: OPEN (1 column, FILENO), ACTIVE (FILENO, CODE, TRANS_DATE). I'm looking to get the FILENO, CODE and last TRANS_DATE. Here's the query I tried but somehow I'm not getting every result.


SELECT B.FILENO, A.CODE, A.TRANS_DATE
FROM OPEN AS B, ACTIVE AS A, (SELECT FILENO, MAX(TRANS_DATE) AS L_DATE FROM ACTIVE GROUP BY FILENO) AS MAXRESULTS
WHERE B.FILENO=A.FILENO AND A.FILENO=MAXRESULTS.FILENO AND A.TRANS_DATE=MAXRESULTS.L_DATE;

Ay suggestions on what I'm doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:31:34

something like


select fileno,code,max(Trans_Date) as LastTranDate
from Active

unless you have other columns in OPEN and need to lookup it for reference

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 03:16:00

Need to add:


GROUP BY fileno,code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 04:30:39

ah...nice catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 05:18:21

I presumed you were just checking if us lot at the back of the class were asleep, or not?

dfeteau
Starting Member

4 Posts

Posted - 2011-09-07 : 10:23:07

I'm a little confused. Are you saying that a subquery is not necessary? That I only need is:

SELECT FILENO, CODE, MAX(TRANS_DATE) AS MAXRESULTS
FROM ACTIVE
GROUP BY FILENO, CODE

I tried that the first time and I get everything. Could there be something wrong with my data?

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 10:29:23

"I get everything"

You should get a list with each combination of FILENO and CODE, and the latest TRANS_DATE for that combination.

If you are getting something else please explain further.

(Depending on the datatypes of your columns if there are varying numbers of leading / trailing spaces they may be treated as being "different" combinations.

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 10:30:28

[code]
SELECT FILENO, CODE, MAX(TRANS_DATE) AS MAXRESULTS
FROM ACTIVE
GROUP BY FILENO, CODE
ORDER BY FILENO, CODE
[/code]
might make it easier to see variations ... there should only be one line for each FILENO / CODE combination

dfeteau
Starting Member

4 Posts

Posted - 2011-09-07 : 10:53:22

Sorry. I realized what I was doing and how I explained it was incorrect. From all the data, I need one FILENO, one CODE (last one), and the max TRANS_DATE. This is how the query should written, correct?


SELECT FILENO, LAST(CODE), MAX(TRANS_DATE) AS L_DATE
FROM ACTIVE
GROUP BY FILENO;

To expand on this idea, I have a short list of FILENO's that I need the last code and date. Would I use a subquery or should I just use an inner join on the query that I have above?

Kristen
Test

22859 Posts

Posted - 2011-09-07 : 11:02:00

No concept of LAST in a relational database, so you'll need to define what the "last" one is. The one with the MAX(TRANS_DATE) perhaps?

Here's a possible solution:



SELECT	FILENO, CODE, TRANS_DATE
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY FILENO
				ORDER BY FILENO, TRANS_DATE DESC, CODE
			),
		FILENO, CODE, TRANS_DATE
	FROM	ACTIVE
) AS T
WHERE	T_RowNumber = 1
ORDER BY FILENO

dfeteau
Starting Member

4 Posts

Posted - 2011-09-07 : 11:52:38

Thank you Kristen & visakh16 . You've been a great help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:44:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources