How to Retrieve Maximum Value of Each Group? - SQL

问题:

There is a table tbl_products that contains data as shown below:

Id   Name  
----------
1    P1       
2    P2       
3    P3       
4    P4       
5    P5       
6    P6       

And another table tbl_inputs that contains data as shown below:

Id   Product_Id   Price   Register_Date
----------------------------------------
1    1              10     2010-01-01
2    1              20     2010-10-11
3    1              30     2011-01-01
4    2              100    2010-01-01
5    2              200    2009-01-01
6    3              500    2011-01-01
7    3              270    2010-10-15
8    4              80     2010-01-01
9    4              50     2010-02-02
10   4              92     2011-01-01

I want to select all products(id, name, price, register_date) with maximum date in each group.

For Example:

Id   Name   Price   Register_Date
----------------------------------------
3    P1     30     2011-01-01
4    P2     100    2010-01-01
6    P3     500    2011-01-01
10   P4     92     2011-01-01

回答1:

select 
  id
  ,name
  ,code
  ,price
from tbl_products tp
cross apply (
  select top 1 price 
  from tbl_inputs ti
    where ti.product_id = tp.id
  order by register_date desc
) tii

Although is not the optimum way you can do it like:

;with gb as (
  select 
    distinct
    product_id
    ,max(register_date) As max_register_date
  from tbl_inputs 
  group by product_id
)
select 
  id
  ,product_id
  ,price
  ,register_date
from tbl_inputs ti
join gb
  on ti.product_id=gb.product_id
  and ti.register_date = gb.max_register_date

But as I said earlier .. this is not the way to go in this case.

回答2:

;with cte as
(
  select t1.id, t1.name, t1.code, t2.price, t2.register_date,
  row_number() over (partition by product_id order by register_date desc) rn
  from tbl_products t1
  join tbl_inputs t2
  on t1.id = t2.product_id
)
select id, name, code, price, register_date
from cte
where rn = 1

回答3:

Something like this..

select id, product_id, price, max(register_date)
from tbl_inputs
group by id, product_id, price

回答4:

you can use the max function and the group by clause. if you only need results from the table tbl_inputs you even don't need a join select product_id, max(register_date), price from tbl_inputs group by product_id, price

if you need field from the tbl_prducts you have to use a join.

select p.name, p. code, i.id, i.price, max(i.register_date)
from tbl_products p join tbl_inputs i on p.id=i.product_id
grooup by p.name, p. code, i.id, i.price

回答5:

Try this:

SELECT id, product_id, price, register_date
FROM tbl_inputs T1 INNER JOIN
(
    SELECT product_id, MAX(register_date) As Max_register_date
    FROM tbl_inputs 
    GROUP BY product_id
) T2 ON(T1.product_id= T2.product_id AND T1.register_date= T2.Max_register_date)

This is, of course, assuming your dates are unique. if they are not, you need to add the DISTINCT Keyword to the outer SELECT statement.

edit

Sorry, I didn't explain it very well. Your dates can be duplicated, it's not a problem as long as they are unique per product id. if you can have duplicated dates per product id, then you will have more then one row per product in the outcome of the select statement I suggested, and you will have to find a way to reduce it to one row per product. i.e: If you have records like that (when the last date for a product appears more then once in your table with different prices)

id  |  product_Id  |  price |  register_date
-------------------------------------------- 
1   |  1           | 10.00  | 01/01/2000
2   |  1           | 20.00  | 01/01/2000

it will result in having both of these records as outcome. However, if the register_date is unique per product id, then you will get only one result for each product id.