I have Name in employee table. I want to extract min length name. if data base contain two name with min. length both should be pick. or second question pick one name only based on alphabetically. When I am doing this like Query:
Select MIN(Name) From Employee;
Result:-Bharti
Why It's Happening?
Name|Length
Meera|5
Sameer|6
Bharti|6
Mahak|5
Bharti|6
Meera|5
MIN() gives the smallest string aphabetically, which is not the shortest string.
To get rows that have the shortest string, you can sort by length and keep the first rows only.
In SQL Server:
select top(1) with ties *
from mytable
order by len(name)
In databases the support the fetch row-limiting clause:
select *
from mytable
order by len(name)
fetch first row with ties
If none of these options is available:
select *
from mytable t
where len(name) = (select min(len(name)) from mytable)
The function that gets the length of a string varies across databases.
`You appear to be asking: Why does the following query return one row?
Select MIN(Name)
From Employee;
The MIN() in the SELECT makes this an aggregation query. It has no GROUP BY. By the definition of SQL, all rows in the table are treated as a single group in this case, and aggregation queries with no GROUP BY always return one row.
If you want all rows with the minimum length, then the length needs to appear somewhere, such as:
select e.name
from employee e
where len(e.name) = (select min(len(e2.name)) from employee);
If you want the first name alphabetically among the shortest names, there are multiple approaches, but a simple one is:
select e.name
from employee e
order by len(e.name), name
fetch first 1 row only;
