Fetch 2 records for each group name of element of column 1 that in column 2 have the most value

I have a table that like below. Now i want to fetch 2 records for each group name of element of column 1 that in column 2 have the most value. For example, Fetch 85 and 75 for A, 65 and 45 for B ...
I use oracle database.
TNX

 ----------------------
 |Column 1 | Column 2 |
 ----------------------
 | A | 85 |
 ----------------------
 | A | 75 |
 ---------------------
 | A | 60 |
 ---------------------
 | A | 50 |
 ---------------------
 | B | 65 |
 ---------------------
 | B | 45 |
 ---------------------
 | B | 35 |
 ---------------------
 | B | 25 |
 ---------------------

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

add a comment |

 ----------------------
 |Column 1 | Column 2 |
 ----------------------
 | A | 85 |
 ----------------------
 | A | 75 |
 ---------------------
 | A | 60 |
 ---------------------
 | A | 50 |
 ---------------------
 | B | 65 |
 ---------------------
 | B | 45 |
 ---------------------
 | B | 35 |
 ---------------------
 | B | 25 |
 ---------------------

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

add a comment |

 ----------------------
 |Column 1 | Column 2 |
 ----------------------
 | A | 85 |
 ----------------------
 | A | 75 |
 ---------------------
 | A | 60 |
 ---------------------
 | A | 50 |
 ---------------------
 | B | 65 |
 ---------------------
 | B | 45 |
 ---------------------
 | B | 35 |
 ---------------------
 | B | 25 |
 ---------------------

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

 ----------------------
 |Column 1 | Column 2 |
 ----------------------
 | A | 85 |
 ----------------------
 | A | 75 |
 ---------------------
 | A | 60 |
 ---------------------
 | A | 50 |
 ---------------------
 | B | 65 |
 ---------------------
 | B | 45 |
 ---------------------
 | B | 35 |
 ---------------------
 | B | 25 |
 ---------------------

sql oracle

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

edited Nov 13 '18 at 9:46

asked Nov 13 '18 at 9:43

Yaser

154

asked Nov 13 '18 at 9:43

Yaser

154

asked Nov 13 '18 at 9:43

Yaser

154

add a comment |

4 Answers
4

active

oldest

votes

You can use row_number() :

select t.*
from (select t.*,
 row_number() over (partition by col1 order by col2 desc) as seq 
 from table t
 ) t
where seq <= 2;

However, fetch first . . . clause also helpful :

select t.*
from table t
where t.col2 in (select t1.col2
 from table t1
 where t1.col1 = t.col1
 order by t1.col2 desc
 fetch first 2 rows only
 );

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

add a comment |

use row_number window function

with t1 as
(
 select col1,col2
 ,row_number() over(partition by col1 order by col2 desc) rn 
 from table_name
) select * from t1 where rn<=2

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

add a comment |

Try using row_number()

select * from
(
select *, row_number() over(partition by col1 order by col2 desc) as rn
from tablename
) A where rn in (1,2)

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

add a comment |

Try this

Here is demo solution

SQL

select col1,col2 from (select col1,col2, rank() over(partition by col1 order by col2 desc) as rn from t) q where q.rn<=2;

answered Nov 13 '18 at 10:26

Brekhnaa

363

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53278064%2ffetch-2-records-for-each-group-name-of-element-of-column-1-that-in-column-2-have%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

You can use row_number() :

select t.*
from (select t.*,
 row_number() over (partition by col1 order by col2 desc) as seq 
 from table t
 ) t
where seq <= 2;

However, fetch first . . . clause also helpful :

select t.*
from table t
where t.col2 in (select t1.col2
 from table t1
 where t1.col1 = t.col1
 order by t1.col2 desc
 fetch first 2 rows only
 );

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

add a comment |

You can use row_number() :

select t.*
from (select t.*,
 row_number() over (partition by col1 order by col2 desc) as seq 
 from table t
 ) t
where seq <= 2;

However, fetch first . . . clause also helpful :

select t.*
from table t
where t.col2 in (select t1.col2
 from table t1
 where t1.col1 = t.col1
 order by t1.col2 desc
 fetch first 2 rows only
 );

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

add a comment |

You can use row_number() :

select t.*
from (select t.*,
 row_number() over (partition by col1 order by col2 desc) as seq 
 from table t
 ) t
where seq <= 2;

However, fetch first . . . clause also helpful :

select t.*
from table t
where t.col2 in (select t1.col2
 from table t1
 where t1.col1 = t.col1
 order by t1.col2 desc
 fetch first 2 rows only
 );

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

You can use row_number() :

select t.*
from (select t.*,
 row_number() over (partition by col1 order by col2 desc) as seq 
 from table t
 ) t
where seq <= 2;

However, fetch first . . . clause also helpful :

select t.*
from table t
where t.col2 in (select t1.col2
 from table t1
 where t1.col1 = t.col1
 order by t1.col2 desc
 fetch first 2 rows only
 );

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

edited Nov 13 '18 at 12:12

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

answered Nov 13 '18 at 9:45

Yogesh Sharma

30.6k51437

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

add a comment |

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

TNX. Your first query is ok, but second query is wrong. The resault of second query is not equal by another.

– Yaser
Nov 13 '18 at 12:11

add a comment |

use row_number window function

with t1 as
(
 select col1,col2
 ,row_number() over(partition by col1 order by col2 desc) rn 
 from table_name
) select * from t1 where rn<=2

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

add a comment |

use row_number window function

with t1 as
(
 select col1,col2
 ,row_number() over(partition by col1 order by col2 desc) rn 
 from table_name
) select * from t1 where rn<=2

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

add a comment |

use row_number window function

with t1 as
(
 select col1,col2
 ,row_number() over(partition by col1 order by col2 desc) rn 
 from table_name
) select * from t1 where rn<=2

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

use row_number window function

with t1 as
(
 select col1,col2
 ,row_number() over(partition by col1 order by col2 desc) rn 
 from table_name
) select * from t1 where rn<=2

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

answered Nov 13 '18 at 9:45

Zaynul Abadin Tuhin

13k21032

add a comment |

Try using row_number()

select * from
(
select *, row_number() over(partition by col1 order by col2 desc) as rn
from tablename
) A where rn in (1,2)

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

add a comment |

Try using row_number()

select * from
(
select *, row_number() over(partition by col1 order by col2 desc) as rn
from tablename
) A where rn in (1,2)

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

add a comment |

Try using row_number()

select * from
(
select *, row_number() over(partition by col1 order by col2 desc) as rn
from tablename
) A where rn in (1,2)

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

Try using row_number()

select * from
(
select *, row_number() over(partition by col1 order by col2 desc) as rn
from tablename
) A where rn in (1,2)

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

edited Nov 13 '18 at 9:53

answered Nov 13 '18 at 9:45

fa06

13.1k2917

answered Nov 13 '18 at 9:45

fa06

13.1k2917

answered Nov 13 '18 at 9:45

fa06

13.1k2917

add a comment |

Try this

Here is demo solution

SQL

select col1,col2 from (select col1,col2, rank() over(partition by col1 order by col2 desc) as rn from t) q where q.rn<=2;

answered Nov 13 '18 at 10:26

Brekhnaa

363

add a comment |

Try this

Here is demo solution

SQL

select col1,col2 from (select col1,col2, rank() over(partition by col1 order by col2 desc) as rn from t) q where q.rn<=2;

answered Nov 13 '18 at 10:26

Brekhnaa

363

add a comment |

Try this

Here is demo solution

SQL

select col1,col2 from (select col1,col2, rank() over(partition by col1 order by col2 desc) as rn from t) q where q.rn<=2;

answered Nov 13 '18 at 10:26

Brekhnaa

363

Try this

Here is demo solution

SQL

select col1,col2 from (select col1,col2, rank() over(partition by col1 order by col2 desc) as rn from t) q where q.rn<=2;

answered Nov 13 '18 at 10:26

Brekhnaa

363

answered Nov 13 '18 at 10:26

Brekhnaa

363

answered Nov 13 '18 at 10:26

Brekhnaa

363

answered Nov 13 '18 at 10:26

Brekhnaa

363

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Pfthb