6
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
Wanted a query to return to me the following table
ID | CATEGORIA | QUANTIDADE 1 | A | 23 1 | B | 15
That is Id that has the largest amount in each category.
Note: Cannot be used partition by
5
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
3
You have equal id and category, so it is a composite primary key, can bring what you look 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
Another way is with a Function window:
SELECT id, categoria, quantidade
FROM (
SELECT id,
categoria,
quantidade,
ROW_NUMBER() OVER( PARTITION BY categoria ORDER BY quantidade DESC ) AS rank
FROM categoria cat
) AS categoria_ranqueada
WHERE categoria_ranqueada.rank = 1
ORDER BY categoria, id
Any problems just inform.
2
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 table row, or a composite key with the ID and CATEGORY, because by looking at the structure you provided taking into account only the ID there is a primary key violation.
This query does not bring the right ID
I edited the answer taking into account this, it is not known whether it is composed inclusive.
Not really. Considering that the ID is a unique key, this query will not bring the ids correctly.
SELECT D.FILM_ID, TITLE,RATING, ALUGADO
 FROM(SELECT B.FILM_ID, COUNT(*) ALUGADO
 FROM RENTAL A, INVENTORY B 
 WHERE A.INVENTORY_ID = B.INVENTORY_ID
 GROUP BY B.FILM_ID
 ORDER BY B.FILM_ID) D, FILM E
 WHERE D.FILM_ID = E.FILM_ID
This is the original query that would give rise to this table that I put with example, I don’t know if it helps much, maaaas ta ai. the name of the database is Pagila, I think it is well known. It is the comic of a video rental company
And only the ID field is primary key?
2
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, this way TABELA.CAMPO.
You have no idea how much it improves the reading (of humans) of your query.
Your colleagues will be very grateful.
I also left an example here, any doubts, just ask. http://sqlfiddle.com/#! 9/7c9d3/2
If it is a table with a very large name can also be used an abbreviation... Exe: FROM CATEGORY as Cat
@Ilario Junior GROUP BY CATEGORIA.CATEGORIA, this works ?
@That must have been just a mistake. For in the query the group by shall refer to the grouping field present in query.
Buenas! No, it wasn’t by mistake. It works, at least in the banks I work with (Firebird and SQL Server). When I have one of the columns(or more) filtering by the larger, or smaller, or even giving a Count(*), you need to group the query by the other columns. Otherwise, what is the sense of being searching for a specific value in the tuple if the other data are not in agreement ?
Browser other questions tagged sql query group-by
You are not signed in. Login or sign up in order to post.
You have two ids equal in the same table? By the way, which bank is?
– Jéf Bueno
Give a check on my answer, it should work.
– Tiago Oliveira de Freitas
Did you get the query you wanted ? I accept an answer as correct
– Marco Souza