0

I would like to get just the max date value (e.g. just the 2014 result), but if I remove the water_type from the group_by it gives an error to do with field not being part of the aggregate.

example:

this query:

SELECT Location_Code, water_type, max(Sampled_Date_Time) as maxdate
FROM [LChem1_Chemistry] lc1
where Location_Code = 'mb340'
and water_type is not null
group by Location_Code, water_type

gets this:

Location_Code   water_type        maxdate
MB340           Group2           2013-09-27 14:00:00
MB340           SubGroup2        2014-03-04 00:00:00

however i only want the 2014 result (but keep the water_type in the resulting table.

thanks

actually this is a better example of input data:

Location_Code   water_type  maxdate
MB117          Group2           2/07/2012 12:58
MB331          Group2          28/02/2013 0:00
MB340          Group2          27/09/2013 14:00
MB340          SubGroup2    4/03/2014 0:00
MB117          Group2           3/07/2012 12:58
MB331          Group2           28/05/2013 0:00

and i want rows 5,7 & 2 in the resulting table.

badgerseatfrogs
  • 107
  • 1
  • 7

2 Answers2

0

What if you try this query:

SELECT Location_Code, water_type, Sampled_Date_Time
FROM [LChem1_Chemistry] lc1
WHERE Location_Code = 'mb340'
AND water_type is not null
ORDER BY Sampled_Date_Time DESC
LIMIT 1
Anton Grigoryev
  • 1,199
  • 11
  • 20
  • i narrowed it down to 1 location for simplicty, but there are more locations. SQL Server by the way. Does limit 1 work for this? – badgerseatfrogs May 01 '14 at 05:20
  • No, it doesn't. I think in this case you should use subquery for getting max date. For example check out this [topic](http://stackoverflow.com/questions/12942306/sql-select-maxdate-and-corresponding-value) – Anton Grigoryev May 01 '14 at 05:30
0

do you want only one row or only rows from 2014

If it is only rows from 2014 then you can add a where clause.

If you want only the top row , then you can have an inner query to return the water_type that you want and use it in the outer_query to return just one row

Biswajit_86
  • 3,661
  • 2
  • 22
  • 36