Max and Min Time query

Question

how to show max time in first row and min time in second row for access using vb6

Answer 1

What about:

SELECT time_value
  FROM (SELECT MIN(time_column) AS time_value FROM SomeTable
        UNION
        SELECT MAX(time_column) AS time_value FROM SomeTable
       )
 ORDER BY time_value DESC;

That should do the job unless there are no rows in SomeTable (or your DBMS does not support the notation).

---

Simplifying per suggestion in comments - thanks!

SELECT MIN(time_column) AS time_value FROM SomeTable
UNION
SELECT MAX(time_column) AS time_value FROM SomeTable
ORDER BY time_value DESC;

---

If you can get two values from one query, you may improve the performance of the query using:

SELECT MIN(time_column) AS min_time,
       MAX(time_column) AS max_time
  FROM SomeTable;

A really good optimizer might be able to deal with both halves of the UNION version in one pass over the data (or index), but it is quite easy to imagine an optimizer tackling each half of the UNION separately and processing the data twice. If there is no index on the time column to speed things up, that could involve two table scans, which would be much slower than a single table scan for the two-value, one-row query (if the table is big enough for such things to matter).