Selecting minimum date in data set

I have a data set that i am attempting to select the first record with a station id of 2.

InspectionNbr   Station          DateTimeStamp
825065             1            2010-11-16 04:38:49.000
825065             2            2010-11-16 12:38:31.000
825065             2            2010-12-06 01:35:14.000
825065             2            2011-01-24 08:11:04.000

In this case i want to select the second line of the results. How can i use SQL to get the minimum date where stationid = 2?

That being stated, this is what i have. i created a temporary table in SQL. i have it setup to pop开发者_JAVA技巧ulate the table with the latest date. Then i attempt and update the temporary table with the following code

UPDATE @report_out 
 set    
DateTimeStamp = Min(si.CreatedDate)
 from
@report_out as r
 INNER JOIN
StationInspection as si
    on si.ModifiedDate = r.DateTimeStamp
where
r.Station = 2

For some reason beyond me it doesn't like the DateTimeStamp = Min(si.CreatedDate) i get the follwing error: An aggregate may not appear in the set list of an UPDATE statement.

any pointers?

As far as I can figure out, an aggregate can't be used in an update statement because the aggregate and the update affect two different row sets. Think about a normal SELECT with an aggregate:

SELECT MIN(CreatedDate)
FROM StationInspection
WHERE Station = 2

The aggregate works on all rows in the row set. The row set is determined by the WHERE clause, which determines which rows will be in the row set.

In an update statement, the WHERE clause determines which rows will be changed:

UPDATE StationInspection
SET CreatedDate = @newDate
WHERE Station = 2

The update affects all rows in the row set (all rows that pass the filter specified by the WHERE clause).

So, in the case where you try to do both (I realize this is somewhat simplified from your code, but it makes the point):

UPDATE StationInspection
SET CreatedDate = MIN(CreatedDate)
WHERE Station = 2

You have two operations that require unique row sets, but only one row set selector (WHERE clause).

SQL doesn't support two WHERE clauses in a single statement. So you'll need two statements:

DECLARE @newDate datetime

SELECT @newDate = SELECT MIN(CreatedDate)
FROM StationInspection
WHERE Station = 2

UPDATE StationInspection
SET CreatedDate = @newDate
WHERE Station = 2

If DateTimeStap is a candidate key (at least when composed with Station), then there is no need to create a temp table; just do:

Select a.* from a join 
(select a.Station, Min(a.DateTimeStamp) as m group by a.Station) as b 
on a.Station = b.Station and a.DateTimeStamp = b.m

then you've got StationID and minimum DateTimeStamp for all Stations. This is a fast Query.

If DateTimeStamp is not a candidate key... The query becomes slow.

If you just want to get the Record with Station id '2' and having minimum date, just try:

SELECT InspectionNbr, Station, DateTimeStamp
FROM   StationInspection
WHERE  Station = 2
AND    DateTimeStamp = (
                          SELECT MIN(DateTimeStamp) 
                          FROM   StationInspection 
                          WHERE  Station = 2
                       )

This way Eliminates Grouping

select T.InspectionNbr,
       T.Station,
       T.DateTimeStamp
from (
       select *,
              row_number() over(order by DateTimeStamp) as rn
       from StationInspection
       where Station = 2
     ) as T
where T.rn = 1  

A shorter statement for some DBs (notably MySQL) might be:

SELECT   InspectionNbr, Station, DateTimeStamp
FROM     StationInspection
WHERE    Station = 2
ORDER BY DateTimeStamp ASC
LIMIT    1