Question: I have a table with a list of folders and I want to return only the top level folders.

For example, if the original table (tblFolders) has a single column (Fldrs) as below:

**Fldrs**

```
C:\Folder1
C:\Folder1\Subfolder1
C:\Folder1\Subfolder2
C:\Folder2\Subfolder1

```

I'd like to return:

```
C:\Folder1
C:\Folder2\Subfolder1

```

Thanks in advance

Answer: 
A standard SQL solution is to UNION all your dates

```
SELECT MyID, MAX(thedate ) AS maxdate, MIN(thedate ) AS mindate
FROM
(
SELECT MyID, Date1 AS thedate FROM table
UNION ALL
SELECT MyID, Date2 AS thedate FROM table
UNION ALL 
SELECT MyID, Date3 AS thedate FROM table
UNION ALL
SELECT MyID, Date4 AS thedate FROM table
) T
GROUP BY MyID

```

There might be better other solutions with window functions, depending on your RDBMS, which you haven't specified. But this one should work with any RDBMS.
In SQL Server, where `least()` and `greatest()` are not available, I would recommend a lateral join to unpivot the columns to rows, and then aggregation to pull out the min and max value:

```
select t.myid, x.*
from mytable t
cross apply (
    select max(dt) maxdate, min(dt) mindate
    from (values (date1), (date2), (date3)) x(dt)
) x

```

This is more efficient than `union`, because it scans the table only once (as opposed to once per `union` member).