CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nico
Nico

Reputation: 1197

get max values per group

Raw data as result of a create view "data" as select [...] statement:

projectId   resourceId  num

1052785922  318153743   10
1052785922  318153743   20
1052785922  318153743   30

1052785936  -2097765361 20
1052785936  318153743   10
1052785936  528513104   30

1052786014  -2097765361 20
1052786014  318153743   10
1052786014  528513104   30

1052786021  -2097765361 20
1052786021  318153743   10
1052786021  528513104   30

1052786099  -2097765361 20
1052786099  318153743   10

I try to filter above data to only get those rows with max(num) per projectId.

estimated results:

projectId   resourceId  num

1052785922  318153743   30
1052785936  528513104   30
1052786014  528513104   30
1052786021  528513104   30
1052786099  -2097765361 20

I know about the possibilities to self left join on num = max(num) , window-function like max(num) over ( partition by projectId ) or CTE, but I wonder if there's any other possibility to just select those with highest value of num.

Background: Above data is only a spot of a large view, which is relatively complex. Since it is a view, I don't think about CTEs or anything this way. The data provided in the view is to feed a planning-app and runtime is quite important. I don't wan't to struggle, ending in performance-expensive view-selects.

The above "raw data" is the result of a view combining data from dozens of tables. I'm looking for a way to filter for the grouped max-values in the creation-statement of this view directly at one shot, without having to put an extra layer or view in between!

Upvotes: 1

Views: 192

Answers (3)

Nico
Nico

Reputation: 1197

I've done it creating the view as select from a UDF to preprocess the data the way Mahmoud supposed to do. UDF allows me to use temp-tables instead of CTE which can be executed multiple times eventually.

Thanks to all for hints!

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

this mega-script doesn't use sorting;) try

SELECT *
FROM dbo.test3 t 
WHERE EXISTS (
              SELECT 1
              FROM dbo.test3                              
              WHERE projectId = t.projectId
              GROUP BY projectId
              HAVING MAX(num) = t.num
              )

Demo on SQLFiddle

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Like this:

WITH CTE
AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY projectId ORDER BY num DESC) rownum
  FROM Table1
)
SELECT projectId,   resourceId,  num
FROM CTE
WHERE rownum = 1;

SQL Fiddle Demo

this will give you:

|  PROJECTID |  RESOURCEID | NUM |
----------------------------------
| 1052785922 |   318153743 |  30 |
| 1052785936 |   528513104 |  30 |
| 1052786014 |   528513104 |  30 |
| 1052786021 |   528513104 |  30 |
| 1052786099 | -2097765361 |  20 |

Upvotes: 3

Related Questions