Group and Sort in Sql

Navigation
0

Hello, good morning, I request a help or guide to try to solve a query in sql, it should be noted that I am new in this world; I am trying to make a query that shows the last record by item code that is to say I have values such as item code, date and cost, I need to structure a query that gives me the last record of that item based on the maximum cost and date.

This is the example of my code. 

select CodItem, Descrip1, Costo, convert(varchar(10),FechaE,105)   as [Fecha de Compra], NumeroD, TipoCom, CodProv, Cantidad

from saitemcom

group by CodItem, Descrip1, Costo, FechaE, NumeroD, TipoCom, CodProv, Cantidad

having TipoCom = 'H'

order by CodItem,FechaE Desc

And my result is this: 

CodItem Descrip1    Costo   Fecha de Compra NumeroD TipoCom CodProv Cantidad
00001   Porta Vaso Ram  159.51000   09-06-2011  00000051    H   313816680   2.00000
00001   Porta Vaso Ram  333.34000   11-03-2011  00001388    H   29849302-0  6.00000
00001   Porta Vaso Ram  210.88000   17-07-2009  00000980    H   314418777   2.00000
00004   Porta Ipod Ram  276.48000   17-07-2009  00000980    H   314418777   1.00000
00005   Porta Iphone Ram    100000.00000    22-04-2017  000053  H   404366709   2.00000
00005   Porta Iphone Ram    276.48000   17-07-2009  00000980    H   314418777   1.00000
000164  Manilla Compuerta Chevrolet Silverado ce    166.50000   26-05-2010  00000201    H   29849302-0  1.00000
00031   Rejilla Stop Toyota Corolla 09  266.07000   26-05-2010  00000201    H   29849302-0  1.00000
001-031 Alarma Up-Grade Alsama  38094.86000 23-11-2016  0000024 H   407318675   5.00000
001-031 Alarma Up-Grade Alsama  31745.72000 22-11-2016  002 H   407318675   1.00000

Now if you can see the code 00001 it is repeated several times I need that code the last record with the highest value and obviously the date and so on with the other codes such as 001-0031 or 000005 which also present the same case.

But I do not know what to look for in order to make this query I can not achieve it with group by, nor with max because then it shows me a single record of the whole table and not one of each or with last_value; I do not know if I'm not grouping the right way.

Could you please guys and girls from the forum!

    
asked by Ivan Ruiz 25.04.2018 в 03:48
source

2 answers

0

Try any of these sentences, adapting them to your table, to see if it works for you.

In the first you are selecting the record with max of Cost over a grouping by coditem ( HERE you have more information)

In the second statement, by using common table expression (CTE).

Perform tests on these statements to learn how they work.

1st OPTION: SELECT CODITEM, MAX (COST) AS PRICE FROM saitemcom GROUP BY CODITEM

2nd OPTION: WITH CTE AS ( select ROW_NUMBER () OVER (PARTITION BY CodItem ORDER BY CodItem, Cost DESC) AS TOTAL, Descrip1, Cost from saitemcom ) select Descrip1, Cost from cte where total = 1

    
answered by 25.04.2018 / 09:43
source
0

At the end, thanks to the help of my friend Raul, I was able to resolve his explanation, leaving the code in this way. 

select it.coditem, it.descrip1, it.costo, it.cantidad, CONVERT(DATE,it.fechae,105) as FechaUC

from saitemcom it

inner join ( select coditem, max(FechaE) as UltimaFC from saitemcom group by coditem) inside on inside.coditem = it.coditem
and inside.UltimaFC = it.fechae 

order by CodItem

The link that he shared with me in spite of being in English was a lot but a lot of help to complete this process.

Without him and the creator of the article, it would have been very difficult for me to achieve it. Thank you very much.

    
answered by 25.04.2018 в 13:15
Doubts about image routes How to redirect from one browser to another