Query To Return First and Last Row Of Table

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Query To Return First and Last Row Of Table

Post by EnginerdUNH »

Hi,

I'm still relatively new to creating queries and am attempting to create a query that will be able to return the first and last rows of the table that I want to pull from. I've so far been able to make individual top value and bottom value queries to pull the rows on their own using the following general syntax:

Top Value:

Code: Select all

Select Top 1 *fields I want to pull* From TableName
Order By *field I want to sort in ascending order*
Bottom Value:

Code: Select all

Select Top 1 *fields I want to pull* From TableName
Order By *field I want to sort in descending order* DESC
Is there an easy way to accomplish both of these individual queries as one complete query or am I destined to create three (top value query, bottom value query, union query)?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query To Return First and Last Row Of Table

Post by HansV »

You can use a UNION query for this, with a little trick because a UNION query allows for only one ORDER BY statement, at the end:

SELECT * FROM (SELECT Top 1 FieldList FROM TableName ORDER BY FieldName)
UNION SELECT * FROM (SELECT Top 1 FieldList FROM TableName ORDER BY FieldName DESC)
ORDER BY FieldName
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Query To Return First and Last Row Of Table

Post by EnginerdUNH »

That worked! Thanks Hans!