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

Learn more tricks

Add Comment

Comments

4th January 2011

This is exactly what I was looking for. Thank you!

ryan

1st December 2011

Newbie question What is the use of where not exists?

redsword

27th November 2012

Thanks, works fine!

Schmid