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's gravatar image

Martin
8.6k114149237
accept rate: 14%

retagged 18 Nov '12, 10:46

Nica%20_SAP's gravatar image

Nica _SAP
866722


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
permanent link

answered 17 Nov '12, 17:48

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 17 Nov '12, 17:49

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

permanent link

answered 16 Nov '12, 14:46

Breck%20Carter's gravatar image

Breck Carter
27.0k424582830
accept rate: 21%

edited 16 Nov '12, 14:47

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.

permanent link

answered 16 Nov '12, 11:25

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
53641220
accept rate: 30%

edited 16 Nov '12, 11:26

I hoped for something more elegant ;-)

(16 Nov '12, 11:36) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×47
×5
×2
×1

question asked: 16 Nov '12, 11:22

question was seen: 1,396 times

last updated: 19 Nov '12, 05:22