February 10, 2017 at 10:28 am
I have this sql statement where I'm attempting to join several tables. But I also need to find the max ld.datum_utc_dt date field.
When I added the max function to the line below, I receive this error
dt_logger.sys_loc_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
SELECT
l.sys_loc_code AS 'Location Name'
,l.logger_code AS 'Logger Name'
,ls.series_name AS 'Series Name'
,ls.series_unit AS 'Unit'
,max(ld.datum_utc_dt) as 'Date'
,ld.datum_value AS 'Value'
,ld.datum_qualifier AS 'Data Qualifier'
FROM dt_logger_datum ld
INNER JOIN dt_logger_series ls ON ld.logger_series_id = ls.logger_series_id
INNER JOIN dt_logger l ON l.logger_id = ls.logger_id
GO
Can someone please help?
Thx...Walter
February 10, 2017 at 10:37 am
walter.dziuba - Friday, February 10, 2017 10:28 AMI have this sql statement where I'm attempting to join several tables. But I also need to find the max ld.datum_utc_dt date field.
When I added the max function to the line below, I receive this errordt_logger.sys_loc_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
SELECT
l.sys_loc_code AS 'Location Name'
,l.logger_code AS 'Logger Name'
,ls.series_name AS 'Series Name'
,ls.series_unit AS 'Unit'
,max(ld.datum_utc_dt) as 'Date'
,ld.datum_value AS 'Value'
,ld.datum_qualifier AS 'Data Qualifier'
FROM dt_logger_datum ld
INNER JOIN dt_logger_series ls ON ld.logger_series_id = ls.logger_series_id
INNER JOIN dt_logger l ON l.logger_id = ls.logger_id
GOCan someone please help?
Thx...Walter
You are attempting to use an aggregate function without a GROUP BY clause.
Without seeing the actual table structure, and some data, it looks as if you want to get the most recent values for ld.datum_value and ld.datum_qualifier, as well as the most recent date.
If you simply add the GROUP BY on all of the columns in the query, you may not get the value you are looking for.
You may want to start here to increase your understanding
Stairway to SQL
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 10, 2017 at 10:53 am
Michael,
thanks for your reply. But I'm stuck and don't understand. I've added group by below, but I'm still getting multiple dates from the table. For example:
2005-01-14 01:00:00
2005-01-14 04:00:00
2005-01-14 05:00:00
2005-01-14 08:00:00
There should only be one max(date) in the output.
SELECT
l.sys_loc_code AS 'Location Name'
,l.logger_code AS 'Logger Name'
,ls.series_name AS 'Series Name'
,ls.series_unit AS 'Unit'
,max(ld.datum_utc_dt) AS 'Date'
,ld.datum_value AS 'Value'
,ld.datum_qualifier AS 'Data Qualifier'
FROM dt_logger_datum ld
INNER JOIN dt_logger_series ls ON ld.logger_series_id = ls.logger_series_id
INNER JOIN dt_logger l ON l.logger_id = ls.logger_id
where series_name='PRECIPITATION_ACCUMULATION'
group by l.sys_loc_code, l.logger_code, ls.series_name, ls.series_unit, ld.datum_value, ld.datum_qualifier
order by max(datum_utc_dt) asc
GO
Walter
February 10, 2017 at 11:27 am
Grouping does exactly that, groups the data. Thus, in your SQL above it will given you the maximum date for each individual group you have defined ( l.sys_loc_code, l.logger_code, ls.series_name, ls.series_unit, ld.datum_value, ld.datum_qualifier). What it sounds like you want, instead, is the OVER Clause (Transact-SQL), which you can use to make use of operators like MAX without GROUP BY.
This is a bit of a stab in the dark, without any sample and expected data, however:MAX(ld.datum_utc_dt) OVER ({PARTITION BY [YourColumn]} ORDER BY ld.Datum_utc_dt) AS 'Date'
You may need a PARTITION, you haven't told us that requirement, so I have put the syntax in there inside the Brackets.
Sorry about the fact the half post is a hyperlink. Seems another problem with the new forum.... When ever I insert a hyperlink, all additional text I type is a hyperlink... Great. -_-
EDIT: Fixed the massive hyperlink by copying the entire post out to Notepad and pasting back, and reinserting the hyperlink after typing this.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 10, 2017 at 11:41 am
Thanks Thom,
I'm still getting a listing of each hour of that day and not the value 2005-01-01 23:00:00 which is the last hour of that day. For example,
2005-01-01 00:00:00
2005-01-01 01:00:00
2005-01-01 02:00:00 etc.
SELECT
l.sys_loc_code AS 'Location Name'
,l.logger_code AS 'Logger Name'
,ls.series_name AS 'Series Name'
,ls.series_unit AS 'Unit'
,max(ld.datum_utc_dt) over (order by ld.datum_utc_dt) as 'Date'
,ld.datum_value AS 'Value'
,ld.datum_qualifier AS 'Data Qualifier'
FROM dt_logger_datum ld
INNER JOIN dt_logger_series ls ON ld.logger_series_id = ls.logger_series_id
INNER JOIN dt_logger l ON l.logger_id = ls.logger_id
where series_name='PRECIPITATION_ACCUMULATION'
GO
thanks for your help...Walter
February 10, 2017 at 12:47 pm
walter.dziuba - Friday, February 10, 2017 11:41 AMThanks Thom,I'm still getting a listing of each hour of that day and not the value 2005-01-01 23:00:00 which is the last hour of that day. For example,
2005-01-01 00:00:00
2005-01-01 01:00:00
2005-01-01 02:00:00 etc.SELECT
l.sys_loc_code AS 'Location Name'
,l.logger_code AS 'Logger Name'
,ls.series_name AS 'Series Name'
,ls.series_unit AS 'Unit'
,max(ld.datum_utc_dt) over (order by ld.datum_utc_dt) as 'Date'
,ld.datum_value AS 'Value'
,ld.datum_qualifier AS 'Data Qualifier'
FROM dt_logger_datum ld
INNER JOIN dt_logger_series ls ON ld.logger_series_id = ls.logger_series_id
INNER JOIN dt_logger l ON l.logger_id = ls.logger_id
where series_name='PRECIPITATION_ACCUMULATION'
GOthanks for your help...Walter
This should give you the max datetime per date
, max(ld.datum_utc_dt) over (partition by cast(ld.datum_utc_dt as date)) as 'Date'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply