Complicated SQL query

Question

I'm trying to build a specific sql query but i have no idea how to implement what i want.
My database looks like this:

Col1 Col2  
"a"   5  
"b"   7  
"a"   8  
"a"   7  
"b"   5  
"b"   7  
"c"   3  
"a"   4  
"b"   3  
"b"   4  
"c"   1 

And I want a query that returns something like this:

"a"   8  
"a"   7  
"b"   7  
"b"   7  

In words: the 2 highest values of the first x strings.

And only putting a limit after sorting doesn't work, because the order by refers to the whole result and not only to one "group" of the result. I hope you understand what i'm up to.

Answer 1

select tbl.col1, tbl.col2 from tbl inner join
(select col2 from tbl order by col2 desc Limit 2) as t1 on
t1.col2 = tbl.col2 order by tbl.col1,tbl.col2

Answer 2

If you can, use a higher-level library like Hibernate for Java, or Spring JDBC template. There are other possibilities in other languages. Don't bother writing SQL in your application, unless you want to lose time.

Edit 1: of course, you may have no choice. You may be interacting with something you didn't write. But if you're creating your own db, when you integrate low-level, bloated, unmaintainable, RDBMS-specific SQL queries in your app, it may be a sign that you are doing something wrong. I'd rather use Hibernate and have the app run a bit slower than spending my development time fixing SQL code. I think "complicated" is something to be avoided in general, if you want to deliver real-world, maintainable applications.

Answer 3

It ain't pretty, but..

SELECT * FROM 
  (SELECT DISTINCT col1, 
          (SELECT col2 FROM tbl WHERE tbl.col1 = a.col1 ORDER BY col2 DESC LIMIT 1) FROM tbl a

  UNION ALL
   SELECT DISTINCT col1,
          (SELECT col2 FROM tbl WHERE tbl.col1 = a.col1 ORDER BY col2 DESC LIMIT 1 offset 1) FROM tbl a)
ORDER BY 1,2 DESC;