The table is in the format below
ID | CATEGORIA | QUANTIDADE 1 | A | 23 2 | A | 12 3 | A | 11 1 | B | 15 2 | B | 10
I want a query to return the following table
ID | CATEGORIA | QUANTIDADE 1 | A | 23 1 | B | 15
Ie Id that has the most amount in each category.
Note: Can not be used partition by
Hello,
See an example below.
declare @tabela table
(
ID int,
Categora char(1),
quantidade int
)
insert into @tabela
values
(1 , 'A' , 23),
(2 , 'A ' , 12),
(3 , 'A' , 11),
(1 , 'B' , 15 ),
(2 , 'B' , 10)
select P.* from
(
select Categora, MAX(quantidade) as quantidade from @tabela
group by Categora
) D
join @tabela P
on P.Categora = D.Categora
and P.quantidade = D.quantidade
You can make a subselect for this.
SELECT a.ID, a.CATEGORIA, (SELECT max(b.QUANTIDADE)
FROM TABELA b WHERE b.CATEGORIA = a.CATEGORIA) AS QUANTIDADE
FROM TABELA a GROUP BY a.CATEGORIA;
Consider a unique ID for each row in the table, or a composite key with the ID and CATEGORY, because by looking at the structure you provided considering only the ID there is a primary key violation.
SELECT CATEGORIA.ID, MAX(CATEGORIA.QUANTIDADE) AS QUANTIDADE
FROM CATEGORIA
GROUP BY CATEGORIA.CATEGORIA
Ahh, a tip, SEMPRE use in your queries, the full name, in this way TABELA.CAMPO .
You have no idea how much it improves your (human) reading of your query.
Your colleagues will be very grateful.
I also left an example here, any questions, just ask. link
You have equal id and category, so it is a composite primary key, you can bring what you are looking for with a subquery in HAVING, so it will work:
SELECT id, categoria, quantidade
FROM categoria cat
GROUP BY id, categoria, quantidade
HAVING quantidade = (SELECT MAX(quantidade) FROM categoria WHERE categoria = cat.categoria)
ORDER BY categoria, id
Any problem is just to inform.