Query to get First and Last record from Table in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

I have bunch of Invoices I need First Invoicenumber and last invoice number in side by side

InvoiceNum
INV001
INV002
INV003
INV004

Output

FirstInvoice LastInvoice
INV001 INV004
Posted 3 months ago

Hi amar,

Refer below sample query.

SQL

DECLARE @tblTest AS TABLE(InvoiceNum NVARCHAR(MAX))
INSERT INTO @tblTest VALUES('INV001')
INSERT INTO @tblTest VALUES('INV002')
INSERT INTO @tblTest VALUES('INV003')
INSERT INTO @tblTest VALUES('INV004')

SELECT TOP 1 InvoiceNum FirstInvoice,
(SELECT  TOP 1 * FROM @tblTest ORDER BY InvoiceNum DESC) LastInvoice
FROM @tblTest

Output

FirstInvoice LastInvoice
INV001 INV004