ASP.NET

max value in SQL CE RSS

6 replies

Last post Dec 21, 2012 05:15 AM by GmGregori

‹ Previous Thread|Next Thread ›

- Reply
Afzaal.Ahmad...

Contributor

2661 Points

1040 Posts
max value in SQL CE

Dec 20, 2012 12:16 PM|LINK
How can I select two cols while one is in max.
```
select max(time) as time from albums_data where album_userid =27;
```
I was using this its going fine.

But If I want to get another col like source col it gives error : In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,source ]
```
select source, max(time) as time from albums_data where album_userid = 27
```
Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~
- Reply
oned_gk

All-Star

31798 Points

6499 Posts

Re: max value in SQL CE

Dec 20, 2012 12:21 PM|LINK
select source, max(time) as time from albums_data where album_userid = 27 group by source

Programming to simplify, dont look for hard way
- Edited by oned_gk on Dec 20, 2012 12:23 PM
- Marked as answer by Afzaal.Ahmad.Zeeshan on Dec 20, 2012 02:44 PM
- Reply
senthilwaits

Contributor

3832 Points

651 Posts
Re: max value in SQL CE

Dec 20, 2012 12:28 PM|LINK
You can use Group by
```
select source, max(time) as time from albums_data where album_userid = 27 group by source
```
Regards,

Senthil Kumar Sundaram
- Marked as answer by Afzaal.Ahmad.Zeeshan on Dec 20, 2012 02:44 PM
- Reply
Afzaal.Ahmad...

Contributor

2661 Points

1040 Posts

Re: max value in SQL CE

Dec 20, 2012 02:49 PM|LINK

oned_gk But this gives me all the fields. While I just want to get one result with the max time.

Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~
- Reply
dblaire

Member

110 Points

26 Posts
Re: max value in SQL CE

Dec 20, 2012 09:40 PM|LINK
- <div class="comment-right-col"> <div>
  As oned_gk and senthilwaits answered:
```
select source, max(time) as time from albums_data where album_userid = 27 group by source
```
  </div> </div>
- <div class="comment-right-col"> <div>
```
The above will show 2 columns, "source" and "time". No other columns should be displayed.
```
  </div> </div>
- Reply
senthilwaits

Contributor

3832 Points

651 Posts
Re: max value in SQL CE

Dec 21, 2012 03:44 AM|LINK
If you want only one row you can use
```
select top 1 source, time from albums_data where album_userid = 27 order by time desc
```
Regards,

Senthil Kumar Sundaram
- Marked as answer by Afzaal.Ahmad.Zeeshan on Dec 21, 2012 08:09 AM
- Reply
GmGregori

Contributor

5470 Points

737 Posts
Re: max value in SQL CE

Dec 21, 2012 05:15 AM|LINK
Try with
```
SELECT * WHERE album_userid = 27 AND time IN (SELECT MAX(time) FROM albums_data WHERE album_userid =27);
```
but senthilwaits's answer is better (I haven't seen it before replying).
- Edited by GmGregori on Dec 21, 2012 07:02 AM