Get multiple rows using select top 1 and joins

Question

I'm working on northwind database.

I have to select the newest and the oldest order/orders (could be multiple with same date).

Is it possible without using subqueries and top 1, only by joining tables?

Answer 1

Using a CTE:

WITH orders_rnk AS (
  SELECT *, RANK() OVER (ORDER BY order_date ASC) AS rnk
  FROM orders
)
SELECT * from orders_rn WHERE rnk = 1;

Using a JOIN:

SELECT o.*
FROM orders AS o
LEFT OUTER JOIN orders AS o2
  ON o.order_date > o2.order_date
WHERE o2.order_id IS NULL;

Answer 2

Yes you can use TOP 1 but add WITH TIES keyword.

link to MS documentation

Per MS documentation

WITH TIES Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.

Exmaple

SELECT TOP 1 WITH TIES 
FROM tableA A JOIN TableB
ON A.ID=B.ID
ORDER BY A.column1