# Select First and Last?

 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 ``` asked 16 Nov '12, 11:22 Martin 8.6k●119●152●237 accept rate: 14% Nica _SAP 866●7●22

 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 ``` answered 17 Nov '12, 17:48 Volker Barth 31.6k●321●465●678 accept rate: 32%
 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 ``` answered 16 Nov '12, 14:46 Breck Carter 27.1k●456●622●894 accept rate: 21% I definetly would like to mark more than one entry as an accepted answer... (19 Nov '12, 05:22) Martin
 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. answered 16 Nov '12, 11:25 Markus Dütting 536●4●12●20 accept rate: 30% I hoped for something more elegant ;-) (16 Nov '12, 11:36) Martin
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×52
×5
×2
×1

question asked: 16 Nov '12, 11:22

question was seen: 1,785 times

last updated: 19 Nov '12, 05:22