Selecting the first or last record 2

[clydejones]

Hi,

Is there a way to select the first or last record of a recordset? Let's say I've entered 300 bottles of beverages sold in a database during a day. Some were milk, some were juice and some were soda. Can I bring back only the first bottle and or last bottle sold?
Thanks,
Clyde

 

[carp]

If you are using Oracle, you will need to include some additional mechanism to ascertain the order of sales. This could take the form of a sequential number (which would also make a good primary key) or a timestamp to indicate the time of sale.

 

[krkX]

Yes you can, here's how to:

To select the first one:
SELECT TOP 1 [data] FROM

ORDER BY [data] ASC

To select the last one:
SELECT TOP 1 [data] FROM

ORDER BY [data] DESC


Hope this does it for you,...


krkX

 

[carp]

But again, this requires some way of determining which was first or last so you know which column to sort on. Assuming you have such a mechanism (e.g., timestamp or sequence number), then you can get everything you need with one query:

SELECT beverage_type, min(time_of_sale) first_sold, max(time_of_sale) last_sold
FROM beverage_sales_table
GROUP BY beverage_type;

BEVERAGE_TYPE FIRST_SOLD LAST_SOLD
------------------- --------------- --------------
Milk 2-MAR-01 5-MAR-01
Orange Juice 27-FEB-01 6-MAR-01
etc
etc

 

[magicrat]

Actually, I think you have solved a different problem than Clyde is asking about. I read his post such that he wants the first and last bottle sold, regardless of type. Your query returns first and last BY type. Maybe that is what he wants, but I've always wrestled with the easiest way to return the entire row based on Min and/or Max value. This is the way I've always done it, but it seems cumbersome every time I do it.

Query 1:
SELECT min(time_of_sale) first_sold, max(time_of_sale) last_sold
Into tbl_first_last_sold_temp
FROM beverage_sales_table

Query 2:
SELECT a.*
From beverage_sales_table a, tbl_first_last_sold_temp b
where (a.time_of_sale = b.last sold)
or (a.time_of_sale = b.first_sold)

Problem with this is that unless the Max and Min(time_of_sale) are not unique, you will get multiple rows returned, but that is a function of the data.
The 2 query method above always seems to give better performance than the subquery method which would be:

SELECT a.*
From beverage_sales_table
where time_of_sale= (Select Max(time_of_sale )
from beverage_sales_table )
or time_of_sale = (Select Min(time_of_sale )
from beverage_sales_table )
This query would actually be fast becase it is not grouping on a large number of values like customer_number for example.

Is there a better way? I'm hoping so, because I have to do this sort of thing alot. A more typical problem for me would be to select the first and last purchase for each customer and capture all of the detail for those purchases:
Query 1:
SELECT customer_number,min(time_of_sale) first_sold, max(time_of_sale) last_sold
Into tbl_first_last_sold_temp
FROM beverage_sales_table
group by customer_number

Query 2:
SELECT a.*
From beverage_sales_table a, tbl_first_last_sold_temp b
where a.customer_number = b.customer_number
and ((a.time_of_sale = b.last sold)
or (a.time_of_sale = b.first_sold))

Greatly appreciate any input/help.
Thanks

 

[MikeJones]

Have you thought of using Inline views, I would expect these to have better performance.

SELECT a.*
From (select max(time_of_sale) max_time_of_sale
from beverage_sales_table) max_ilv,
(select min(time_of_sale) min_time_of_sale
from beverage_sales_table) min_ilv,
beverage_sales_table
where time_of_sale = Max_time_of_sale
or time_of_sale = Min_time_of_sale

 

[bitbrain]

Variation:

select *
from beverage_sales
where time_of_sale = (select min(time_of_sale) from beverage_sales)
or time_of_sale = (select max(time_of_sale) from beverage_sales)