Top 50 records by DATALENGTH of a field in SQL Server

Question

Azure SQL Server 2016 - I'm having trouble with the syntax to combine TOP, MAX and DATALENGTH to get a list of the Top 50 records from a table by DATALENGTH of a specific field.

The field I need to do the DATALENGTH on is called Text. I don't actually want the Text field returned in the results - What I want returned are fields called CaptureId and TaskSourceId from the 50 records with the largest DATALENGTH in the Text field, as well as the DATALENGTH amount.

I tried this, but it did not work, with an error about CaptureId not being contained in an aggregate function or GROUP BY clause.

SELECT TOP 50 
    CaptureId, 
    TaskSourceId, 
    MAX(DATALENGTH([Text]))
FROM 
    Data.Capture

Can someone please help me correct this query?

score 2 · Accepted Answer · answered Sep 07 '18 at 22:24

2

You don't need max. Just sort the records:

select top 50 
  captureid, tasksourceid, datalength(text) as text_len
from data.capture
order by datalength(text) desc

This will not care about ties and return up to 50 rows.

If you want to account ties regarding text field data length, use top 50 with ties instead of simple top 50. It may return more than 50 records in this case.

answered Sep 07 '18 at 22:24

Kamil Gosciminski

16,547
8
49
72

Hi Kamil, it seems you posted your answer while I was editing. I edited to mention that I also need to have the actual `DATALENGTH` of the `Text` field returned as well. – Stpete111 Sep 07 '18 at 22:27
Ah, I see you just edited your answer. Thanks! I will try this now and mark as the answer asap. – Stpete111 Sep 07 '18 at 22:28
Exactly what I needed. Thanks Kamil. – Stpete111 Sep 07 '18 at 22:42

Top 50 records by DATALENGTH of a field in SQL Server

1 Answers1