0

Let's say I have data like this :

|  id      |    code     |   name   |  number |
-----------------------------------------------
|   1      |      20     |   A      |    10   |
|   2      |      20     |   B      |    20   |
|   3      |      10     |   C      |    30   |
|   4      |      10     |   D      |    80   |

I would like to group rows by code value, but get real rows back (not some aggregate function).

I know that just 

 select * 
 from table 
 group by code

won't work because database don't know which row to return where code is the same.

So my question is how to tell database to select (for example) the lower number column so in my case 

|  id      |    code     |   name   |  number |
-----------------------------------------------
|   1      |      20     |   A      |    10   |
|   3      |      10     |   C      |    30   |

P.S.

I know how to do this by PARTITION but this is only allowed in Oracle databases and can't be created in JPA criteria builder (what is my ultimate goal).

Marko Zadravec
  • 8,298
  • 10
  • 55
  • 97
  • @damien_the_unbeliever: I don't think this is a duplicate because Marko needs to do this in the obfuscation layer, not in SQL. –  May 12 '15 at 07:54
  • @damien_the_unbeliever I think to, this is not the duplicate. The second reason is that in other example the criteria is on id column (first id) in my case criteria is on number column. I know it seams like not much of a change but if I want to use join (select...) solution I need in second select to fetch id without aggregation function (in other case was min). – Marko Zadravec May 12 '15 at 08:02

2 Answers2

0

Why You don't use code like this?

SELECT 
        id,
        code,
        name,
        number
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY code ORDER BY number ASC) AS RowNo
        FROM table
) s
WHERE s.RowNo = 1
Maryan Oksentyuk
  • 271
  • 2
  • 6
0

You can look at this site;

Data Partitioning

Onur Cete
  • 263
  • 1
  • 2
  • 10