Re: [SQL] Finding the "most recent" rows

Your script doesn't show how to get the "most recent" rows.

True enough, but that's just because of the < 3 condition within the
subselect. Take that out and you'll get all the most recent rows (in
this example that means largest order_no, but the same works with
dates or whatever).

What are you trying to show here?

The first query was just showing the table, the second the action of
the relevant select.

I thought this was what you wanted and that you would recognize the
effect of the < 3 condition. I just happened to have this example
already that illustrated the general idea and expected that you would
get the idea. Sorry if I should have been more complete.

I think you could just select the highest id, which means the latest data input.

In this particular case the id and the order_no column happen to yield
the same results (they are both ordered in the same way). That
generally won't be the case. But, if you want the highest id within
each customer category a simple change to the select below will do it.

In any case, to get the largest (or most recent or whatever) anything
in each category you need to do a condition involving a subselect with
an aggregate. Something like the following will give you the largest
(without additional constraint) order_no within each customer.

select * from invoices r
where order_no = (select max (order_no) from invoices r2 where r.customer = r2.customer)
order by r.customer, r.order_no;

Sorry for the confusion.

Cheers,
Brook