max (date) help! - 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) help!

Author

Topic

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 10:13:01

I can't seem to figure out why I get the results that I do.

Here is my data:

Table name: TRANS

Data:

Spool, Status, Action_Date
--------------------------------------------
001 loaded 4/6/10
001 shipped 4/8/10
002 staged 4/5/10
002 loaded 4/6/10

Desired output:

Spool, Status, Action_Date
--------------------------------------------
001 shipped 4/8/10
002 loaded 4/6/10

Basically, I want to return only the row for each spool with the most recent date. This is my current select statement:

SELECT Spool, Status, Action_Date,
(SELECT Action_Date WHERE Action_Date = MAX(Action_Date))
FROM TRANS
GROUP BY Spool, Status
ORDER BY Spool;

It is returning all the rows from my table and not filtering out the older dates. Where am I going wrong?

thanks for any help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 10:18:46

select Spool, Status, Action_Date
from
(
select
row_number() over (partition by Spool order by Action_Date DESC) as rownum,*
from Trans
)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 10:26:42

I tried this but received this error:

'row_number' is not a recognized function name.

I am using SQL Server 2000. Perhaps I should mention that first. Sorry. Should this work with my version?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-16 : 10:26:45

Your desired result doesn't make sense for the data you have supplied.


SELECT Spool, Status, MAX(Action_Date) AS Action_Date,
FROM TRANS
GROUP BY Spool, Status
ORDER BY Spool;

Would give you all rows still, but taking Spool out would give you your desired results but without spool, so to get that you could do:


declare @TRANS Table
(
Spool varchar(3), Status varchar(50), Action_Date datetime
)
insert into @TRANS
select '001','loaded','2010-04-06'
union select '001','shipped','2010-04-08'
union select '002','staged','2010-04-05'
union select '002','loaded','2010-04-06'

SELECT MAX(Spool.Spool) Spool, MaxSpool.Status, MaxSpool.Action_Date
FROM
(
	SELECT Status, MAX(Action_Date) AS Action_Date
	FROM @TRANS
	WHERE Status <> 'Staged'
	GROUP BY  Status
) MaxSpool
INNER JOIN @TRANS Spool
ON Spool.Status = MaxSpool.Status
AND Spool.Action_Date = MaxSpool.Action_Date
GROUP BY MaxSpool.Status, MaxSpool.Action_Date
ORDER BY Spool

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 10:45:59

Here is one way:


SELECT 
	T.*
FROM
	@Trans AS T
INNER JOIN
	(
		SELECT Spool, MAX(Action_Date) AS Action_Date
		FROM @Trans
		GROUP BY Spool
	) AS D
	ON T.Spool = D.Spool
	AND T.Action_Date = D.ACtion_Date

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 11:00:25

[code]
SELECT spool,status,Action_Date from @TRANS t2 where exists
(
SELECT top 1 Action_Date
FROM @TRANS t1
where t1.Spool=t2.Spool and t1.Action_Date<t2.Action_Date

)

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 11:02:28

Lamprey,

When I try your method, I get the following error:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'Action_Date'.

What would cause this?

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 11:12:55

Idera,

Thank you! Your solution worked for me.

thanks all for your suggestions. One thing that's becoming painfully obvious to me is that there are many ways to accomplish the desired output in SQL. I'm not sure if this is good or bad, as I'm still very new to all of this.

Thanks again!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 12:13:38

quote:
Originally posted by The Sweg

Lamprey,

When I try your method, I get the following error:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'Action_Date'.

What would cause this?

I'm not sure. I just ran against the table varibale that RickD scripted. If you have changed the code to go against your real table and it is not working, feel free to post your code and we should be able to help you fix it.

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 12:38:00

OK, now with the select statement working, I'm having one more tiny issue. Well, maybe a snidge bigger than 'tiny'. anyways, Of course this is not the 'big picture' of what's going on, what I'm actually doing is using this Select statement to create a view from one database table for use in another database. It has always worked well in the past, but I just filtered it to show only spools that had a status of 'Shipped'. Now, we are wanting to see the current status for all spools.
Here's my problem; with using the above select statement from Idera, I get the most current status (of which I'm thankful), but it limits the output of the other tables to just show the spools that have a shipping status. I need all of my spools returned, whether they exist in the shipping table or not. Is there an easy way to modify this statement so that I can still get rows in my final output of spools that have no shipping status?

I hope I'm not being too confusing...

thanks again for any help you could extend.

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 13:27:40

quote:
Originally posted by The Sweg

Idera,

Thank you! Your solution worked for me.

You are welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 13:28:17

quote:
Originally posted by The Sweg

OK, now with the select statement working, I'm having one more tiny issue. Well, maybe a snidge bigger than 'tiny'. anyways, Of course this is not the 'big picture' of what's going on, what I'm actually doing is using this Select statement to create a view from one database table for use in another database. It has always worked well in the past, but I just filtered it to show only spools that had a status of 'Shipped'. Now, we are wanting to see the current status for all spools.
Here's my problem; with using the above select statement from Idera, I get the most current status (of which I'm thankful), but it limits the output of the other tables to just show the spools that have a shipping status. I need all of my spools returned, whether they exist in the shipping table or not. Is there an easy way to modify this statement so that I can still get rows in my final output of spools that have no shipping status?

I hope I'm not being too confusing...

thanks again for any help you could extend.

Just post your expected output the way you did before so that it's more easy to get a grasp of what exactly you need.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

The Sweg
Starting Member

29 Posts

Posted - 2010-06-16 : 14:00:27

Idera,

I'll try to simplify my example data and output and apply your answers to my real situation. (My full SQL statement fills up 12 pages at 11x17 when printed from notepad!

Table-1: Spools
Data:
Spool FinalQC Plant_No
-----------------------------
001 Yes 3
002 Yes 2
003 No 2
004 Yes 3

Table2: TRANS
Data:
Spool Status Action_Date
---------------------------------
001 Loaded 4/6/10
001 Shipped 4/8/10
002 Staged 4/5/10
002 Loaded 4/6/10

Desired output:

Spool FinalQC Plant_No Status Action_Date
----------------------------------------------------
001 Yes 3 Shipped 4/8/10
002 Yes 2 Loaded 4/6/10
003 No 2
004 Yes 3

I still want the output of all the spools (even 003 and 004) because there is information about them contained in other tables, even if they haven't been shipped yet and therefore have no shipping data in the TRANS table.

I hope this helps.

thanks again for your effort!

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 14:12:05

Just use a Right Outer Join of the resultset which you get using my query with the table Spools.
Example



select * from
(
  My Query

)T Right join Spools on Spools.Spool=T.Spool

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-16 : 14:47:12

Juse as an FYI, if you have a Spool with only 1 row Idera's method will not select that row. I'm not sure if this is a valus use case though.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-06-17 : 05:08:48

No, but mine would if you remove the where..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources