Latest and next-to-latest query?

I use a number of queries where I get the "latest" price/quantity/whatever. I
rely on the id's being in order (they are for us) and do this...
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID IN
(SELECT MAX(priceid) FROM tblprices GROUP BY
name)
Now I need to change this slightly, in addition to the latest price, I need
to get the "next-to-latest". My first attempt...
SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
p1.Price1 - p2.Price1 AS diff
FROM tblPrices p1, tblPrices p2
WHERE p1.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
GROUP BY accountid) AND (p2.PriceID IN
(SELECT MAX(priceid)
FROM tblprices
WHERE priceid NOT IN
(SELECT
MAX(priceid)
FROM
tblprices
GROUP BY accountid)
GROUP BY accountid))
This works, and I can live with that, but its very slow. Can anyone suggest
another way to go about this?
Maury
Hi
I meant PK--Primary Key
SELECT p.name, p.price
FROM tblPrices p
WHERE p.PriceID =(SELECT MAX(priceid) FROM tblprices
tp WHERE tp.PK=p.PK)
--OR
SELECT p.name, p.price
FROM tblPrices p
join
(SELECT MAX(priceid),name FROM tblprices GROUP BY name) AS D
ON d.PK=p.pk
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14FBC159-36EA-4460-8CBE-BE7C9035AFF0@.microsoft.com...
>I use a number of queries where I get the "latest" price/quantity/whatever.
>I
> rely on the id's being in order (they are for us) and do this...
> SELECT p.name, p.price
> FROM tblPrices p
> WHERE p.PriceID IN
> (SELECT MAX(priceid) FROM tblprices GROUP BY
> name)
> Now I need to change this slightly, in addition to the latest price, I
> need
> to get the "next-to-latest". My first attempt...
> SELECT p1.name, p1.Price1 AS latestPrice, p2.Price1 AS olderPrice,
> p1.Price1 - p2.Price1 AS diff
> FROM tblPrices p1, tblPrices p2
> WHERE p1.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> GROUP BY accountid) AND (p2.PriceID IN
> (SELECT MAX(priceid)
> FROM tblprices
> WHERE priceid NOT IN
> (SELECT
> MAX(priceid)
> FROM
> tblprices
> GROUP BY
> accountid)
> GROUP BY accountid))
> This works, and I can live with that, but its very slow. Can anyone
> suggest
> another way to go about this?
> Maury