CODEWITHSUNDEEP
sql
Oliveira
Oliveira

Reputation: 1321

SQL Group by one column

so I have this table:

ID  INITIAL_DATE    TAX
A   18-02-2012      105
A   19-02-2012      95
A   20-02-2012      105
A   21-02-2012      100
B   18-02-2012      135
B   19-02-2012      150
B   20-02-2012      130
B   21-02-2012      140

and what I need is, for each distinct ID, the highest TAX ever. And if that TAX occurs twice I want the record with the highest INITIAL_DATE.

This is the query I have:

SELECT   ID, MAX (initial_date) initial_date, tax
    FROM t t0
   WHERE t0.tax = (SELECT   MAX (t1.tax)
                       FROM t t1
                      WHERE t1.ID = t0.ID
                   GROUP BY ID)
GROUP BY ID, tax
ORDER BY id, initial_date, tax

but I want to believe there is a better way of grouping these records.

Is there any way of NOT grouping by all the columns in the SELECT?

Upvotes: 0

Views: 611

Answers (3)

Lamak
Lamak

Reputation: 70638

Have you tried with analytical functions?:

SELECT t0.ID, t0.INITIAL_DATE, t0.TAX
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TAX DESC , INITIAL_DATE DESC) Corr
        FROM t) t0
WHERE t0.Corr = 1

Upvotes: 2

Mursa Catalin
Mursa Catalin

Reputation: 1449

i have tested and this is a solution that works 

SELECT  t1.ID ,
    MAX(t1.data) ,
    t1.tax FROM    test t1
    INNER JOIN ( SELECT ID ,
                        MAX(tax) as maxtax
                 FROM   test
                 GROUP BY ID
               ) t2 ON t1.ID = t2.ID
                       AND t1.tax = t2.maxtax GROUP BY t1.ID ,t1.tax

Upvotes: 1

GeT
GeT

Reputation: 550

As far as I know all the columns in a SELECT that are not aggregated in one or another way, must be part of the GROUP BY statement.

Upvotes: 1

Related Questions