1

A question I got on my last interview:

Following is the sql table data:
Column1   Column2   Column3
  1          8         9
  2          8         7
  3          9         5
  4          9         6
  5          9         2
  6          10        3
  7          8         1
  8          9         4
  9          10        8
  10         8         10

Asked to write a sql query to show the columns with highest value in column3 for each different value in column 2 without using sub query like following output:

Column1   Column2   Column3
  4          9         6
  9          10        8
  10         8         10

I have tried using group by on column2 as : 

SELECT column1,column2, MAX(column3) AS col3 FROM test1 GROUP BY column2

But it is giving following result:

Column1   Column2   Column3
  1          8         10
  3          9         6
  6          10        8

Can anyone please help me to get my query correct ?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
user2845060
  • 58
  • 5
  • 3
    Must be MySQL or SQLite because that query is an error on any other RDBMS. – Bill Karwin May 15 '14 at 02:16
  • @BillKarwin . . . Actually Postgres since 9.1 supports this as well, assuming that `Column1` is defined as a primary key (http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-GROUPBY). This is even ANSi standard behavior. – Gordon Linoff May 15 '14 at 02:19
  • Forgot to mentioned database. It is MySQL. – user2845060 May 15 '14 at 02:29
  • @GordonLinoff, thanks, that's interesting. That's news to me that it's ANSI behavior. I thought the [single-value rule](https://mariadb.com/kb/en/the-single-value-rule/) was pretty strict. – Bill Karwin May 15 '14 at 04:06
  • @BillKarwin . . . Unfortunately, I don't know how to access the standard document on-line. This is an optional feature and where the terminology "functionally dependent" comes from. Here is a blog that references it (http://ocelot.ca/blog/blog/2014/01/26/mysql-group-by-select-lists-and-standard-sql/). – Gordon Linoff May 15 '14 at 12:48
  • @GordonLinoff, The final SQL spec is not free, but you can find [SQL 2003 drafts for download](http://rpbouman.blogspot.com/2005/11/sql-2003-drafts-for-download.html). – Bill Karwin May 15 '14 at 15:00

0 Answers0