MS SQL Tips & Tricks

How to find first and last occurrence of entry given kind

Finding first and last occurrence of record of some type is quite simple. We can do it on example of invoices. Let's assume that we have table containing the list of invoices

Invoices Table:

ID CustomerNo InvoiceNo Date
1 12 1 2009-01-07
2 15 2 2009-02-08
3 13 3 2009-03-09
4 18 4 2009-04-10
5 18 5 2009-05-11
6 12 6 2009-06-12
7 13 7 2009-07-13

Now is the time to find first invoice for each customer 

SELECT
select * from Invoices i
where not exists(select ii.Id 
			from Invoices ii
			where ii.CustomerNo=i.CustomerNo and
				ii.Id<i.Id
		)
order by i.CustomerNo

The query above, will give you result of:

ID CustomerNo InvoiceNo Date
1 12 1 2009-01-07
3 13 3 2009-03-09
2 15 2 2009-02-08
4 18 4 2009-04-10

Next try to find last invoice

select * from Invoices i
where not exists(select ii.Id 
			from Invoices ii
			where ii.CustomerNo=i.CustomerNo and
				ii.Id>i.Id
		)
order by i.CustomerNo

The query above, will give you result of:

ID CustomerNo InvoiceNo Date
6 12 6 2009-06-12
7 13 7 2009-07-13
2 15 2 2009-02-08
5 18 5 2009-05-11

Queries above assumes that all invoices were entered in correct date order, if it might not be right than maybe you should change the condition ii.Id<i.Id to ii.Date<i.Date and ii.Id>i.Id to ii.Date>i.Date


Download SQL script

See also


Learn more tricks

Add Comment

Comments