Get only one item from a subgroup values

Question

I have this values in my SQL table:

A Column | B Column | C Column 
------------------------------
11       | Text1    | 10
11       | Text2    | 20
11       | Text3    | 30
12       | Text4    | 20
12       | Text5    | 15
12       | Text9    | 12
13       | Text60   | 01
13       | Text99   | 05
13       | Text33   | 09
13       | Text0    | 04

And I'd like to get only the line in each subgroup delimited by 'A Column' where I have the highest value in 'C Column'. Eg.: I'll get:

A Column | B Column | C Column 
------------------------------
11       | Text3    | 30
12       | Text4    | 20
13       | Text33   | 09

Could someone help me? I'm still learning about sql.

Thanks!

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Mar 24 '15 at 18:16

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You can do it as below in t-sql, MS SQL Server:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY A_Column ORDER BY C_Column DESC) AS rn
   FROM table_name
)
SELECT *
FROM cte
WHERE rn = 1

Please find similar example here: Get top 1 row of each group

edited May 23 '17 at 12:28

Community

1
1

answered Mar 24 '15 at 18:18

Tushar

3,527
9
27
49

void · Accepted Answer · 2015-03-24T18:29:04.487

1

as you didn't specified the RDBMS system so I want to provide a query which works for almost all of RDBMSs which supports SQL (use a self join with group by like below):

select tb2.id,tb1.b,tb1.c from 
(select t1.b,max(t1.c) c from table_name t1 group by tb1.b) tb1
join table_name tb2 on tb1.b=tb2.b and tb1.c=tb2.c

edited Mar 24 '15 at 18:29

answered Mar 24 '15 at 18:22

void

7,760
3
25
43

In your query you used two tables, but I'm working with just one. Why do you use two? – Rafael Carmanini Mar 24 '15 at 19:06
1

the query uses only one table, review it again – void Mar 24 '15 at 19:15
Hmmm. Had neglected to modify my answer for all databases. Just came across with my answered questions. @Farhęg +1 for correct answer :) – Tushar Apr 03 '15 at 15:12
@Avidan, thank you bro, your answer is also perfect for each RDBMS which supports row_number and cte, just accept my apologize if I not upvote it because upvoting each other is not valid in SO. – void Apr 03 '15 at 15:58
@Farhęg Hmmm. Bro. Right is right. Answers are upvoted only when those are perfectly right.according to question. I never mind :) ...And am much junior to you.... Here just to learn and explore few things/ Technologies. :) – Tushar Apr 04 '15 at 09:16
@Avidan, please don't say that, you are not junior to me because there is too much things that you know but I don't know specially in IT(RDBMS/Programming,Etc), anyway. good luck and all the bests for you. – void Apr 04 '15 at 10:06

Get only one item from a subgroup values

2 Answers2