|
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 |
|
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. 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 |
|
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
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. I hoped for something more elegant ;-)
(16 Nov '12, 11:36)
Martin
|
