CODEWITHSUNDEEP
sql-servervb.net-2010
Ibanez1408
Ibanez1408

Reputation: 5078

Can I get the First and Last Transaction in a single query in Sql Server?

I'm trying to get the First Receipt Number and the Last Receipt Number for a particular day. Is there a way to get it in one statement? I can get it using 2 statements like so:

cnn.Open()
query = "select top 1(invoice) from invoice_tbl where transaction_date = @transaction_date order by invoice Desc"
cmd = new sqlCommand(query,cnn)
.......

and

cnn.Open()
query = "select top 1(invoice) from invoice_tbl where transaction_date = @transaction_date order by invoice Asc"
cmd = new sqlCommand(query,cnn)
.......

Can I get booth values in a single statement and place it in a variable so that I can place it on two labels separately?

Thanks!

Upvotes: 0

Views: 220

Answers (2)

M.Ali
M.Ali

Reputation: 69554

Another way would be something like.....

SELECT invoice
FROM (
select   invoice
        ,ROW_NUMBER() OVER (order by invoice DESC) Last_Invc
        ,ROW_NUMBER() OVER (order by invoice ASC ) First_Invc
from invoice_tbl 
where transaction_date = @transaction_date 
) t
WHERE Last_Invc = 1
   OR First_Invc = 1

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Try this

select 
(select top 1(invoice) 
from invoice_tbl 
where transaction_date = @transaction_date order by invoice Desc) as last_inv ,
(select top 1(invoice) 
from invoice_tbl 
where transaction_date = @transaction_date order by invoice Asc) as First_inv

Upvotes: 2

Related Questions