CODEWITHSUNDEEP
c#asp.netsqlsql-server
user1027620
user1027620

Reputation: 2785

sql server select min value from matching datetime

SELECT Id, Value, Sender, Recipient, MIN(DateTime), TypeOf, VideoId, Likes, Comments
FROM xx.dbo.StreamView
WHERE StreamView.TypeOf = 3 AND StreamView.[DateTime] >= "Some DateTime Value Here"

I am trying to get the latest record directly newer than the given DateTime value.

when executing this query I am receiving the following error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Some DateTime Value Here'.

Also, when removing this line AND StreamView.[DateTime] >= "Some DateTime Value Here"

I am getting:

Msg 8120, Level 16, State 1, Line 1 Column 'xx.dbo.StreamView.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Suggestions? Thanks.

Update

WITH
  sequenced_records AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY [DateTime] DESC) AS sequence_id,
    *
  FROM
    xx.dbo.StreamView
  WHERE
    StreamView.TypeOf = 3
    AND StreamView.[DateTime] >= "2011-12-29 01:38:21.607"
)
SELECT
  *
FROM
  sequenced_records
WHERE
  sequence_id = 1

returns -> Msg 207, Level 16, State 1, Line 11 Invalid column name '2011-12-29 01:38:21.607'.

Update 2

guys I need to return just a SINGLE record with the minimum datetime in a group of selected items bigger than the given datetime.

Upvotes: 3

Views: 6444

Answers (3)

Tim M.
Tim M.

Reputation: 54377

If you are going to use an aggregate function like MIN(), you must group the non-aggregate columns using a GROUP BY clause.

See http://msdn.microsoft.com/en-us/library/ms177673.aspx

You also need to replace the double quotes in "Some DateTime Value Here" with single quotes.

Try this code:

SELECT 
    Id, 
    Value, 
    Sender, 
    Recipient, 
    MIN([DateTime]), 
    TypeOf, 
    VideoId, 
    Likes, 
    Comments
FROM xx.dbo.StreamView

WHERE StreamView.TypeOf = 3 AND StreamView.[DateTime] >= '1/1/2012'

GROUP BY
    Id, 
    Value, 
    Sender, 
    Recipient, 
    TypeOf, 
    VideoId, 
    Likes, 
    Comments

Per the updated question:

DECLARE @Date DATETIME = (
    SELECT MIN( [DateTime] ) FROM xx.dbo.StreamView WHERE TypeOf = 3 
        AND [DateTime] >= '1/1/2012' 
);

SELECT TOP 1 * FROM xx.dbo.StreamView WHERE [DateTime] >= @Date;

Upvotes: 2

BrokenGlass
BrokenGlass

Reputation: 160902

You can use a sub query instead:

SELECT Id, Value, Sender, Recipient, DateTime, TypeOf, VideoId, Likes, Comments
FROM xx.dbo.StreamView
WHERE StreamView.TypeOf = 3 
AND StreamView.[DateTime] = (Select MIN(DateTime) from xx.dbo.StreamView where [DateTime] >= 
'Some DateTime Value Here' AND TypeOf = 3)

Upvotes: 0

MatBailie
MatBailie

Reputation: 86725

MIN() is an aggregate, and as the error says; you must use GROUP BY to use aggregate queries. This means that you can't get the MIN() of a group and have the individual values from one record in the group at the same time.

There are two alternatives...
- Use GROUP BY to get the MIN() and then use that to look up the record it matches.
- Use an alternative approach, such as ROW_NUMBER() 

WITH
  sequenced_records AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY [DateTime] ASC) AS sequence_id,
    *
  FROM
    xx.dbo.StreamView
  WHERE
    StreamView.TypeOf = 3
    AND StreamView.[DateTime] >= "Some DateTime Value Here"
)
SELECT
  *
FROM
  sequenced_records
WHERE
  sequence_id = 1

NOTE You do need real datetime in there, such as '01012012 23:34'


For environments without ROW_NUMBER() you'd revert to the aggregate approach, using two steps. Such as...

SELECT
  *
FROM
  StreamView
WHEREE
  TypeOf = 3
  AND DateTime = (SELECT MIN(DateTime) FROM StreamView WHERE TypeOf = 3 AND DateTime >= '01012012 23:34')

Upvotes: 1

Related Questions