Help with MAX date

[ClareMoore]

I have a table that looks like

event_id, activity_name, operator_name, start_date, end_date

I want to select all columns, and select an extra column which contains the LAST end_date value from the whole table. I'd expect this column to contain the same value in each row, which is the latest date



can you help?

[Rixxe]

This might be helpful:
http://techonthenet.com/sql/max.php


Post your code if you get stuck, sure someone here can help you.

[ClareMoore]

Hi there and thanks

i'd already looked at that page but i'm still not getting the results i need

I tried:

SELECT start, end, MAX(end) as MaxEnd
FROM mw_activity_data
group by start, end

But the maxend getting returned is just the end from each row, i want this last column to be the maximum end from teh whole table repeated

Thanks

[ClareMoore]

The results i'm getting look like:

start end MaxEnd
26/01/2011 6:00:00 AM 26/01/2011 6:00:30 AM 26/01/2011 6:00:30 AM
26/01/2011 6:00:30 AM 26/01/2011 6:03:58 AM 26/01/2011 6:03:58 AM
26/01/2011 6:03:58 AM 26/01/2011 6:21:45 AM 26/01/2011 6:21:45 AM
26/01/2011 6:21:45 AM 26/01/2011 6:25:45 AM 26/01/2011 6:25:45 AM

which is not what i want

[TheShabz]

So you want
26/01/2011 6:00:00 AM 26/01/2011 6:00:30 AM 26/01/2011 6:25:45 AM
26/01/2011 6:00:30 AM 26/01/2011 6:03:58 AM 26/01/2011 6:25:45 AM
26/01/2011 6:03:58 AM 26/01/2011 6:21:45 AM 26/01/2011 6:25:45 AM
26/01/2011 6:21:45 AM 26/01/2011 6:25:45 AM 26/01/2011 6:25:45 AM

?

[ClareMoore]

Yes exactly, do you know how i go about achieving this?

Thanks very much

[TheShabz]

Why would you want to store that in a table? That's something that you would query for on the fly when its needed.

[ClareMoore]

I am writing a query ......
the results i just said i wanted are what i want FROM my query?

The table looks like:

start end
26/01/2011 6:00:00 AM 26/01/2011 6:00:30 AM
26/01/2011 6:00:30 AM 26/01/2011 6:03:58 AM
26/01/2011 6:03:58 AM 26/01/2011 6:21:45 AM
26/01/2011 6:21:45 AM 26/01/2011 6:25:45 AM

I need help writing a query that will select start, end and MaxEnd, giving the results i posted above

[TheShabz]

You can get the max end time but it wont simply populate a full column in a query.

I can see you wanting to pull the max time
SELECT max(End) As MaxEnd
FROM myTable;

I can also see you wanting to pull the record that contains the max time
SELECT Top 1 *
FROM myTable
ORDER BY End DESC;

I dont see, however, why you would want all the of records and an extra column that contains the max. Also, my apologies about the table assumption. I figured thats where you were going with it.

[ClareMoore]

Because I'm experimenting with creating a report in SQL Server Reporting services, using a third party chart control, and it would make my life a lot easier if i could have this coming back with the dataset.

Does that mean it cant be done?

[ClareMoore]

I can only have one dataset per report using this third party tool, which is why i was hoping ot append the MAX to a separate column

[TheShabz]

You would have to store it in another table as I cant think of a way to pull it with one query.

you would create a SELECT query that holds the maxEnd. Then use an update query to update a blank field MaxEndDate with DLookup("maxEnd", "queryName")

[ClareMoore]

I think i figured it out, but i had to use an extra field, machine_id in my query, sorry i shoud have added that to my example at the beginning

SELECT
STT.FirstBegin,
STT.FinalEnd,
*
FROM mw_activity_data
INNER JOIN (
SELECT mach_id,
min(start) as FirstBegin,
max(end) as FinalEnd
FROM mw_activity_data
GROUP BY mach_id
) STT ON mw_activity_data.mach_id = STT.mach_id
ORDER BY STT.mach_id,
start

[TheShabz]

As long as you got it working, nothing else really matters.
Cheers!