Select First and Last?

[MCMartin]

Does any short form exist to select only the first and the last entry of a query result-set?

Something like:

Select First C1, last C1 from table where x=y order by z

[VolkerBarth · Accepted solution]

I guess Breck's OLAP solution is the way to go. Another approach would be to use join two derived tables using FIRST with one ordered in ascending and one in descending order (as Markus has suggested):

SELECT first_row_num, 
       last_row_num
FROM (SELECT FIRST row_num AS first_row_num FROM RowGenerator ORDER BY row_num ASC ) dtFirst,
     (SELECT FIRST row_num AS last_row_num  FROM RowGenerator ORDER BY row_num DESC) dtLast

which obviously returns the same set as Breck's sample.
If you don't need to have both values in one single row, combining both derived queries in a UNION ALL query would be efficient, methinks, too:

SELECT 'first', * FROM
  (SELECT FIRST row_num AS first_row_num FROM RowGenerator ORDER BY row_num ASC ) dtFirst
UNION ALL
SELECT 'last',  * FROM
  (SELECT FIRST row_num AS last_row_num  FROM RowGenerator ORDER BY row_num DESC) dtLast
ORDER BY 1

[Breck_Carter]

Will you settle for LESS elegant?

SELECT TOP 1 
       first_row_num, 
       last_row_num
FROM ( SELECT row_num,
              FIRST_VALUE ( row_num ) OVER ( ORDER BY row_num ) AS first_row_num,
              LAST_VALUE ( row_num ) OVER ( ORDER BY row_num )  AS last_row_num
         FROM RowGenerator
     ) AS RowGenerator
 ORDER BY row_num DESC;

first_row_num,last_row_num
1,255

[MCMartin]

I definetly would like to mark more than one entry as an accepted answer...

[Former Member]

Possibly not what you searched for but

select first C1 from table where x=y order by z desc

should get you the last C1.

[MCMartin]

I hoped for something more elegant 😉