CODEWITHSUNDEEP
sqlsql-server
Coding Duchess
Coding Duchess

Reputation: 6909

Selecting second largest length of a field in a table

I am using the following query to return the length of the longest value for Field1:

SELECT MAX(LEN(Field1))
FROM Table1

How can I modify my query to return second largest length for Field1?

Upvotes: 0

Views: 81

Answers (4)

SlimsGhost
SlimsGhost

Reputation: 2909

Window Functions are usually the most efficient way to do this, and they are ultimately flexible to fit varying requirements. Here is an example (tested on Azure SQL DB):

select f1, lenf, lenf_dense_rank
from (
        SELECT 
            f1, 
            lenf=len(f1), 
            lenf_dense_rank=dense_rank() over (order by len(f1) desc)
        FROM table1
) x
where lenf_dense_rank = 2

Again, depending on your needs, you may want to use rank() versus dense_rank(), but Window Functions are still the technique to use for situations like this. Highly suggest looking them up and learning more about them - they should be used way more often than they currently are.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

In the inner query, you can select the maximum length of the column and eliminate it in the outer query and do a max so you get the second largest length in that column.

select max(val) as secondlargest
from t join
(
SELECT max(len(val)) as lf1
FROM t
) x
on len(t.val) < x.lf1

If you need all the values with the second largest length, it is best to use ctes. SQL Fiddle

with top2 as (
select top 2 len(val) as lf1
from t
order by len(val) desc
) ,
scnd as 
(select min(lf1) as snd from top2)
select val
from t join scnd 
on len(t.val) = scnd.snd

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SQL Server 2012+ supports OFFSET/FETCH:

select len(f1)
from table1
group by len(f1)
order by len(f1) desc
offset 1
fetch first 1 row only;

In earlier versions there are various methods. Here is one:

with ml as (
      select len(f1) as lenf
      from table1
      group by len(f1)
     )
select max(lenf)
from ml
where lenf < (select max(lenf) from ml);

Upvotes: 3

Robbert Kooiman
Robbert Kooiman

Reputation: 157

If you only need the second one:

SELECT MAX(Field1) FROM Table1
WHERE Field1 NOT IN (SELECT MAX(Field1) FROM Table1)

Do note that this is only correct when the two maximum values are not the same. If they are, the second answer will still be selected. For example:

1: 500 points
2: 500 points
3: 410 points

2 will still be selected, but it's debatable which answer you need.

Upvotes: 0

Related Questions