-2

Help me please with query: 

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 approximately such table

Car / price_base / 08-08-2016:13-40 / 100 /   
Car / price_base / 08-08-2016:14-40 / 150 /    
Car / price_base / 08-09-2016:13-40 / 190 /    
Car / price_super / 08-09-2016:18-40 / 210 /   
Car / price_super / 08-10-2016:13-40 / 290 /

I want to return

Car / price_base / 08-09-2016:13-40 / 190 / 
Car / price_super / 08-10-2016:13-40 / 290 /

That is good, types of the price of date and their value. Prompt please how to get the last (the current price for each type of the price and each goods) tried options with group but there is obviously not enough skill. 

nom.id - PK SKU     
pt.id - PK price type   
p.id -PK price    
p.parentext - parent price (sku)    
p.sp327 - FK to price type   
date = date column

I am using SQL Server 2008.

Table structure 

T=1SCONST |

----Columns-------
Name                  |Descr               |Type|Length|Precision
F=ROW_ID                |Row ID              |I   |0     |0        
F=OBJID                 |ID obj(0-cons )     |C   |9     |0        
F=ID                    |ID parameter        |I   |0     |0        
F=DATE                  |Fix date            |D   |0     |0        
F=VALUE                 |valume              |V   |255   |0        
F=DOCID                 |ID Document         |C   |9     |0        
F=TIME                  |Time                |I   |0     |0        
F=ACTNO                 |Action No           |I   |0     |0        
F=LINENO_               |LineNo              |S   |0     |0        
F=TVALUE                |                    |C   |3     |0      

T=SC319   |

----Columns-------
Name                  |Descr               |Type|Length|Precision
F=ROW_ID                |Row ID              |I   |0     |0        
F=ID                    |ID object           |C   |9     |0        
F=PARENTEXT             |Parent in other tabl|C   |9     |0        
F=ISMARK                |Object is Marked for|L   |0     |0        
F=VERSTAMP              |Version stamp       |I   |0     |0        
F=SP6681                |(P)Âàëþòà           |C   |9     |0        
F=SP6682                |(P)Ïðîöåíò          |N   |5     |2        
F=SP327                 |(P)ÒèïÖåí           |C   |9     |0      

T=SC319   |Ñïðàâî÷íèê Öåíû               |SC319      |R         

# ----Columns-------
# Name                  |Descr               |Type|Length|Precision
F=ROW_ID                |Row ID              |I   |0     |0        
F=ID                    |ID object           |C   |9     |0        
F=PARENTEXT             |Parent in other tabl|C   |9     |0        
F=ISMARK                |Object is Marked for|L   |0     |0        
F=VERSTAMP              |Version stamp       |I   |0     |0        
F=SP6681                |(P)Âàëþòà           |C   |9     |0        
F=SP6682                |(P)Ïðîöåíò          |N   |5     |2        
F=SP327                 |(P)ÒèïÖåí           |C   |9     |0        

#==TABLE no 24     : Ñïðàâî÷íèê Íîìåíêëàòóðà
# Name    |Descr                         |SQLTableNam|RecordLock
T=SC84    |Ñïðàâî÷íèê Íîìåíêëàòóðà       |SC84       |R         

# ----Columns-------
# Name                  |Descr               |Type|Length|Precision
F=ROW_ID                |Row ID              |I   |0     |0        
F=ID                    |ID object           |C   |9     |0        
F=PARENTID              |ID parent obj       |C   |9     |0        
F=CODE                  |object code         |C   |8     |0        
F=DESCR                 |object description  |C   |70    |0        
F=ISFOLDER              |Is Line - Folder    |Y   |0     |0        
F=ISMARK                |Object is Marked for|L   |0     |0        
F=VERSTAMP              |Version stamp       |I   |0     |0        
F=SP85                  |(P)Àðòèêóë          |C   |25    |0        
F=SP86                  |(P)ÁàçîâàÿÅäèíèöà   |C   |9     |0        
F=SP208                 |(P)Âåñîâîé          |N   |1     |0        
F=SP2417                |(P)ÂèäÍîìåíêëàòóðû  |C   |9     |0        
F=SP97                  |(P)ÌèíÎñòàòîê       |N   |13    |3        
F=SP5066                |(P)ÍåÂêëþ÷àòüÂïðàéñ |N   |1     |0        
F=SP5013                |(P)ÍîìåðÃÒÄ         |C   |9     |0        
F=SP94                  |(P)ÎñíîâíàÿÅäèíèöà  |C   |9     |0        
F=SP4427                |(P)ÎñíîâíîåÑâîéñòâî |C   |9     |0        
F=SP103                 |(P)ÑòàâêàÍÄÑ        |C   |9     |0        
F=SP104                 |(P)ÑòàâêàÍÏ         |C   |9     |0        
F=SP5012                |(P)ÑòðàíàÏðîèñõîæäåí|C   |9     |0        
F=SP8574                |(P)ÍèçÎòêëîíåíèÿÑåáå|N   |4     |2        
F=SP8575                |(P)ÂåðõÎòêëîíåíèÿÑåá|N   |4     |2        
F=SP8576                |(P)ÍèçÎòêëîíåíèÿÑïåö|N   |4     |2        
F=SP8577                |(P)ÂåðõÎòêëîíåíèÿÑïå|N   |4     |2        
F=SP8578                |(P)ÍèçÎòêëîíåíèÿÇàêó|N   |4     |2        
F=SP8579                |(P)ÂåðõÎòêëîíåíèÿÇàê|N   |4     |2        
F=SP8580                |(P)ÍèçÎòêëîíåíèÿÐîçí|N   |4     |2        
F=SP8581                |(P)ÂåðõÎòêëîíåíèÿÐîç|N   |4     |2        
F=SP8599                |(P)Äëèíà            |N   |6     |2        
F=SP8600                |(P)Øèðèíà           |N   |6     |2        
F=SP8601                |(P)Âûñîòà           |N   |6     |2        
F=SP8602                |(P)Îáúåì            |N   |14    |5        
F=SP8606                |(P)ÌàêñèìàëüíûéÏðîöå|N   |4     |2        
F=SP8607                |(P)Àäðåñ            |C   |9     |0        
F=SP95                  |(P)Êîììåíòàðèé      |M   |0     |0        
F=SP101                 |(P)ÏîëíÍàèìåíîâàíèå |M   |0     |0
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0xdeface
  • 259
  • 2
  • 11
  • 2
    Please provide the table and column structures. Also, are you using sql-server-2005 or sql-server-2008? – Ash Aug 24 '16 at 02:48
  • 1
    I think you've posted what the query currently shows you. Now post what you _want_ it to return – Nick.Mc Aug 24 '16 at 05:21
  • i want return: Car / price_base / 08-09-2016:13-40 / 190 / and Car / price_super / 08-10-2016:13-40 / 290 / – 0xdeface Aug 24 '16 at 05:26
  • I will edit the question for you and add that info – Nick.Mc Aug 25 '16 at 00:42
  • Here is an example of how to do what you want: http://stackoverflow.com/questions/6201253/how-to-get-the-last-record-per-group-in-sql. I can adapt it for you if you like but you need to give me some columns names in your example data. Please *edit* the question and add column names to your sample data. If you do that I will come back with an answer. – Nick.Mc Aug 25 '16 at 01:26

1 Answers1

0
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
0xdeface
  • 259
  • 2
  • 11