Distinct needed, but with other columns, possible?

[jakeisstoked]

Hi,
I need what I think should be a pretty simple SQL statement, but I just can't seem to find the solution.
It's only selecting data from 1 table..

Ok, so I have a table of prices associated with items, and the prices have a date (so it acts as kind of log as well), and there are also different types of prices; so I have a list of price types with an associated date, and the price.
e.g.

tbl_Prices
----------------
price_id | price | price_type | date

pretty simpe, no?

some example data:

10 | $100 | 'delivery' | 10/10/2005
11 | $500 | 'purchase' | 02/10/2005
12 | $20 | 'storage' | 08/10/2005
13 | $150 | 'delivery' | 12/10/2005

so basically this table will get pretty big, with any number of price changes, and also any number of 'types' of prices.

I need to select, from this table, all fields, from each price_type, but only the latest record for each type. I understand how distinct works, but it only applies to a single record, or a whole row (in which case all records are selected, because the are all different if distinct is applied to the row).

For example I wan't the result to be:

11 | $500 | 'purchase' | 02/10/2005
12 | $20 | 'storage' | 08/10/2005
13 | $150 | 'delivery' | 12/10/2005

Only selecting the latest record for each type of price.

This should be very simple, yes?
I can't work it through...
Any help? Bare in mind there can be any number of price types, all with a lot of updates...

This I can see how to solve, if I split the tables up into, say, tbl_latest_prices and tbl_price_log, but I don't want to, it's bad practice, and I have to control the logic with the forms/interface/whatever, and I would have redundant attributes.

Thanks for any ideas.

-Jake

 

[r937]

select price_id
     , price
     , price_type
     , [date]
  from tbl_Prices as xx
 where [date]
     = ( select max([date])
           from tbl_Prices
          where price_type = xx.price_type )

r937.com | rudy.ca

 

[jakeisstoked]

Argh, so simple, thanks a million, mate.
-Jake

 

[cjany]

I have a very similar problem and was excited to see a remedy, so I used this code, but still can't get it to work correctly. I have cs_id, counselor_id, prty_num. My recordset may look like this

cs_id counselor_id prty_num
12345 AB 1
98765 CD 1
56789 EF 1
56789 GK 2

where prty_num represents the assignment of the counselor.

The prty_num "1" for case id 56789 was replaced by prty_num "2"

I only want the record with prty_num "2" (or the last one assigned) to display in my query.

Any help will be appreciated.
Cheryl

 

[r937]

compare this --

select price_id
     , price
     , price_type
     , [date]
  from tbl_Prices as xx
 where [date]
     = ( select max([date])
           from tbl_Prices
          where price_type = xx.price_type )

with this --

select cs_id         
     , counselor_id          
     , prty_num
  from tbl_Cheryl as xx
 where prty_num
     = ( select max(prty_num)
           from tbl_Cheryl
          where cs_id = xx.cs_id )

notice any similarities? you coulda done that


r937.com | rudy.ca