SELECT placeName ,picURL, counterDate , MAX(counter)
FROM Place
WHERE convert(varchar(10), counterDate, 102) = convert(varchar(10), getdate(), 102)
GROUP BY counterDate
it didnt work i want the row with the max counter column
SELECT placeName ,picURL, counterDate , MAX(counter)
FROM Place
WHERE convert(varchar(10), counterDate, 102) = convert(varchar(10), getdate(), 102)
GROUP BY counterDate,placeName ,picURL
Programming to simplify, don't look for difficult way
Suwandi - Non Graduate Programmer
Hiii thanku for ur reply but that didn't work I tried it I get all rows cause they are grouped by the place name I need the result to be the row with the max counter
As far as I know, this error means sql couldn't find the placeName ,picURL columns which is selected by the group.
If we use group by, we could only select the group by's column and aggregate functions (SUM, MIN, MAX, etc.).
So you will get this error.
I suggest you could write a database demo to us and write a result demo to us, so that it will be more easily for us to find the solution to achieve your requirement.
For example:
Database table:
a b
--------1 abc
1 def
1 ghi
2 jkl
2 mno
2 pqr
The result I want to achieve：
a x
--------1 ghi
2 pqr
Best Regards,
Brando
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
SELECT placeName ,
picURL ,
counterDate , counter
FROM Place
WHERE CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)
AND counter = ( SELECT MAX(counter)
FROM Place
WHERE CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102)
);
Please Mark as Answer if find helpful -- Nasser -- Skype: maleknasser1
LinkedIn: https://www.linkedin.com/in/maliknasser
Member
22 Points
109 Posts
aggregate function done wrongly
Oct 20, 2016 12:27 PM|arwa55|LINK
hiii im trying to this code
it didnt work i want the row with the max counter column
i keep getting
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
how this can be done in another way
All-Star
52262 Points
15556 Posts
Re: aggregate function done wrongly
Oct 21, 2016 12:55 AM|oned_gk|LINK
Suwandi - Non Graduate Programmer
Member
22 Points
109 Posts
Re: aggregate function done wrongly
Oct 21, 2016 06:43 AM|arwa55|LINK
Star
9501 Points
2979 Posts
Re: aggregate function done wrongly
Oct 22, 2016 12:24 PM|Brando ZWZ|LINK
Hi arwa55,
As far as I know, this error means sql couldn't find the placeName ,picURL columns which is selected by the group.
If we use group by, we could only select the group by's column and aggregate functions (SUM, MIN, MAX, etc.).
So you will get this error.
I suggest you could write a database demo to us and write a result demo to us, so that it will be more easily for us to find the solution to achieve your requirement.
For example:
Database table:
The result I want to achieve：
Best Regards,
Brando
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Star
13450 Points
2764 Posts
Re: aggregate function done wrongly
Oct 22, 2016 04:23 PM|Nasser Malik|LINK
Hi Arwa,
You can do it like this way
SELECT placeName , picURL , counterDate ,counter FROM Place WHERE CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102) AND counter = ( SELECT MAX(counter) FROM Place WHERE CONVERT(VARCHAR(10), counterDate, 102) = CONVERT(VARCHAR(10), GETDATE(), 102) );
Skype: maleknasser1
LinkedIn: https://www.linkedin.com/in/maliknasser
Member
22 Points
109 Posts
Re: aggregate function done wrongly
Oct 24, 2016 08:11 AM|arwa55|LINK
it worked perfectly thanku