select top 10 longest posts

Question

i am trying to view the lengthiest posts on stackoverflow using:

http://odata.stackexchange.com/stackoverflow/query/new

i am doing:

select max(body) from posts;​

and it returns:

You'd be looking to static link (as opposed to dynamically link)

I'm not sure how many of the MS redistributables statically link in.

anyone know what i am doing wrong?

Answer 1

How about something like

select top 10 len(body) from posts order by len(body) desc​

Answer 2

select top 10 body from posts order by len(body) desc;

Answer 3

Select Top 10 Title, Len(Body) As BodyLength
From Posts
Order By Len(Body) Desc

Answer 4

It looks like you want something like:

select top 10 body from posts order by len(body) desc

Answer 5

The max function returns the maximum value of an expression. What (I think) you want is a combination of top and len(body).

select top 10 len(body), Id as [Post Link] 
from posts
order by len(body) desc

Answer 6

max(body) Doesnt return the longest message but the last message if you sort alphabetically. in our case it starts with

You'd be looking ...

Answer 7

MAX is an aggregate function. When dealing with a numeric column data type, it will return the highest value. For character columns, MAX finds the highest value in the collating sequence. Either way, it will only return one value per group - if no groups are specified, it will only return one value.

That leaves you with needing to get the length of the body so you can order the results - you have two options:

• LEN returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
• DATALENGTH returns the number of bytes used to represent any expression. DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data. The DATALENGTH of NULL is NULL.

So you'd want to use the following query:

  SELECT TOP 10 p.body
    FROM POSTS p
ORDER BY DATALENGTH(p.body) DESC