Hello, help with the request please:

select * from sc84 as nom join sc319 as p on p.PARENTEXT = nom.id join sc219 as pt on p.sp327 = pt.id join _1SCONST as c on c.objid=p.id

As a result, I have about the same table

 Чайник / закупочная / 08-08-2016:13-40 / 100 / Чайник / закупочная / 08-08-2016:14-40 / 150 / Чайник / закупочная / 08-09-2016:13-40 / 190 / Чайник / оптовая / 08-09-2016:18-40 / 210 / Чайник / оптовая / 08-10-2016:13-40 / 290 /

That is, goods, types of price dates and their values. Tell me, please, how to get the latest (the actual price for each type of price and each product) I tried out the options with grouping, but obviously there is not enough skill.

  • nom.id - PK products
  • pt.id - PK price types
  • p.id - PK prices
  • p.parentext - price owner
  • p.sp327 - foreign key on price type
  • date column is called date

2 answers 2

Judging by the comments you probably have MS-SQL, which version and which window functions it supports, unfortunately, you didn’t specify in the question. We assume that you have the row_number () function. In this case, from any query, you can select the maximum lines like this:

  select * from ( select *, ROW_NUMBER() over(partition by nom.id,pt.id order by c.date desc) NUM from sc84 as nom join sc319 as p on p.PARENTEXT = nom.id join sc219 as pt on p.sp327 = pt.id join _1SCONST as c on c.objid=p.id ) A where NUM=1

In partiton by the OVER clause, we need to specify the columns in the context of which we need maxima and set the correct sorting order. row_number with it will number the records and those that will be with numbers 1 and will be necessary to us.

    Hi, look at this option:

     select nom.id, pt.id, p.id, p.parenttext,p.sp327,p.date from sc84 as nom join ( select pt.id, p.id, p.parenttext,p.sp327,p.date from sc319 p1 outer apply (select max(p2.date) maxdate from sc319 p2 where p2.id = p1.id ) as maxdate where p.date = maxdate ) as p on p.PARENTEXT = nom.id join sc219 as pt on p.sp327 = pt.id join _1SCONST as c on c.objid=p.id

    Update

    similarly, you can make a request with _1SCONST, in outer apply, select the maximum date for objid, then take the price value from _1SCONST for a specific objid and its maximum date

     select nom.id, pt.id, p.id, p.parenttext,p.sp327,с.date, c.value from sc84 as nom join sc319 p on p.PARENTEXT = nom.id join sc219 as pt on p.sp327 = pt.id join ( select c1.objid, c1.VALUE /* сюда выбираете цену */ from _1SCONST c1 outer apply (select max(c2.date) maxdate from _1SCONST c2 where c2.objid = c1.objid ) as maxdate where c.date = maxdate ) as с --join _1SCONST as c on c.objid=p.id