Menu
How can I get first record of a table and last record of a table in one result-set?
This Query fails
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC Any help?
705
It is not possible to use two different ORDER BY in the UNION statement. UNION returns single resultsetand as per the Logical Query Processing Phases.
Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set. Let us see an example.
After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name ). You can modify the sorting order (ascending or descending) separately for each column.
Put your order by and top statements into sub-queries:
select first.Id, first.Name from ( select top 1 * from Locations order by Id) first union all select last.Id, last.Name from ( select top 1 * from Locations order by Id desc) last If you're working on SQL Server 2005 or later:
; WITH NumberedRows as ( SELECT Id,Name, ROW_NUMBER() OVER (ORDER BY Id) as rnAsc, ROW_NUMBER() OVER (ORDER BY Id desc) as rnDesc FROM Locations ) select * from NumberedRows where rnAsc = 1 or rnDesc = 1 The only place this won't be like your original query is if there's only one row in the table (in which case my answer returns one row, whereas yours would return the same row twice)
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
