get Max value without group by 1 row sql query

Question

i need help to get a value from a table without grouping one field.

the table design is like this

Progress    DocNum    Project
---------------------------
30        10         1111
70        11         1111
30        10         1112
100       12         1111
70        13         1112

then i want data like this. get the max progress for each project, without grouping DocNum

Progress    DocNum    Project
---------------------------
100       12         1111
70        13         1112

how someone can help me. thankyou very much before

I added the greatest-n-per-group tag. This question has been answered hundreds of times on Stack Overflow. Follow the tag and see the solutions. — Bill Karwin
– Bill Karwin, Commented Feb 26, 2017 at 2:05

Gurwinder Singh · Accepted Answer · 2017-02-26 03:19:15Z

2

You can use window functions row_number to get one row with max progress per project:

Select *
From (
  Select t.*,
    Row_number() over (partition by project order by progress desc) rn
  From your_table t
) t where rn = 1

If there are multiple rows with max progress per project and you want to retrieve all of them, use rank instead:

Select *
From (
  Select t.*,
    Rank() over (partition by project order by progress desc) rnk
  From your_table t
) t where rnk = 1

Note:

Both the above queries read the table only once. The accepted solution reads the table twice.

edited Feb 26, 2017 at 3:19

answered Feb 26, 2017 at 2:57

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pham X. Bach Over a year ago

Should we use ROW_NUMBER() or rank for this? I think with that we will have to calculate row_number for each record so this will become a big problem with large dataset.

Pham X. Bach Over a year ago

I didn't blame you, I just want to ask. After seaching with ROW_NUMBER VS MAX, I got the aswer is It Depends :)). For a simple mind, not count for index, optimize..., your query's complexity is like for x in 1..n (for y in 1..x) and be O(n* n), and the above solution is MAX + IN (or JOIN) = O(n) + O(n* n) = O(n* n) too

Gurwinder Singh Over a year ago

@PhamX.Bach - Of course it depends. Sometimes, you'll think correlated queries will be slower but they outperform the uncorrelated queries. In data world, a fast solution now may not be as fast tomorrow.

Pham X. Bach Over a year ago

Yep, and so we have CBO :x

Pham X. Bach · Accepted Answer · 2017-02-26 02:39:36Z

1

You could use this

SELECT t.progress, t.docnum, t.project
FROM table_name t
INNER JOIN (
    SELECT MAX(progress) max_progress,
        project
    FROM table_name
    GROUP BY project
) mt
ON t.project = mt.project
    AND t.progress = mt.progress;

Or this

SELECT progress, docnum, project
FROM table_name 
WHERE (progress, project) IN (
        SELECT MAX(progress) max_progress,
            project
        FROM table_name
        GROUP BY project
    );

answered Feb 26, 2017 at 2:39

Pham X. Bach

5,4624 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

get Max value without group by 1 row sql query

2 Answers 2

Note:

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Note:

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related