CODEWITHSUNDEEP
sqlt-sqlgroup-bydistinct
Oiproks
Oiproks

Reputation: 794

Select distinct rows by first column and max of second

I have the following table:

Item           Prod        Company
1.00961.501    PR011798    ditto
1.00961.501    PR012042    ditto
1.00961.501    PR013442    Pika
1.00961.502    PR012043    ditto
1.00961.503    PR011959    ditto
1.00961.503    PR011669    Bulb
1.00961.507    PR014783    ditto
1.00961.507    PR012050    ditto

I would like to select all the table grouped by Item taking only the max Prod. Something like this:

Item           Prod        Company
1.00961.501    PR012042    ditto
1.00961.502    PR012043    ditto
1.00961.503    PR011959    ditto
1.00961.507    PR014783    ditto

I tried the following:

SELECT  DISTINCT Item, MAX(Prod)
FROM    DataBase
WHERE   Company = 'ditto'

but it gives me

Column 'Item' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I remove the MAXclause it returns no errors, but Item repeats for each Prod.

Any Idea?

EDIT

I forgot to add the Where clause to the question. As I do that, and try to use Group By instead of Distinct I get the following error:

Column 'Company' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 447

Answers (2)

Sahathulla
Sahathulla

Reputation: 324

You need to partition the product and then select one from that partition.

You should try this below query.

  select Item, Prod, Company from 
  (
    select *,
    (ROW_NUMBER() over (partition by Item order by Prod DESC)) as Row from [table_name] 
    where Company = 'ditto'
  ) t where Row = 1

Upvotes: 0

mtalhk
mtalhk

Reputation: 26

Your query should be like this;

SELECT  Item, MAX(Prod),Company
FROM    DataBase
WHERE   Company = 'ditto'
GROUP BY Item,Company;

Upvotes: 1

Related Questions