how to select the row which comes first alphabetically and integer wise?

Question

I have the table:

ID | title1 | year1 | title2 | year2 | title3 | year3
5  |  ghost | 2006  | runner | 2007  | alpha  | 2008
5  |  ghost | 2006  | runner | 2007  | beta   | 2008
6  |  Fight | 2007  | guard  | 2008  | Jack   | 2009
6  |  Steam | 2010  | Pilot  | 2011  | save   | 2012

So I need a query that returns:

ID | title1 | year1 | title2 | year2 | title3 | year3
5  | ghost  | 2006  | runner | 2007  | alpha  | 2008
6  | Steam  | 2010  | Pilot  | 2011  | save   | 2012

Best way of going about this? I was thinking of joining the table by itself and doing some operations but haven't had any luck.

the condition is that if ID has two different titles under the same year, then pick the title that comes first alphabetically, else pick the largest 3 consecutive years

Could you state which by which columns do you need it sorted? Is it title1 and ID? — Paweł Duda, Mar 10 '15 at 17:20
I return alpha because alpha comes first before beta alphabetically — user125535, Mar 10 '15 at 17:24
@shA.t because the condition is that if ID has two different titles under the same year, then pick the title that comes first alphabetically, else pick the largest 3 consecutive years — user125535, Mar 10 '15 at 17:25
I'm not sure if postgress has it, but on sql server I'd use this in a CTE or derived table and add this to the select column list: `ROW_NUMBER() OVER (PARTITION by ID ORDER BY Title1) AS RowID ` then in the outer query you can filter on `WHERE RowID=1` — KM., Mar 10 '15 at 17:37
Please update the question. `else pick the largest 3 consecutive years` is unclear, does not correspond to your example, either. `title` is unclear: there are three different title columns. — Erwin Brandstetter, Mar 10 '15 at 21:39
@ErwinBrandstetter apparently the person who answered my question understood it. Also how does that not correspond to my example? — user125535, Mar 10 '15 at 22:52
"The *row with the* largest three consecutive rows". I understood 3 rows when I read it. And `title` is obviously unclear. Could you just clarify? — Erwin Brandstetter, Mar 10 '15 at 22:58
If it helps, ID is an actor, titles are the movie titles that the actor was involved in. title1 is when movie1 was released, title2 is when movie2 was released and title3 is when movie3 was released. Select the row (grouped by ID) with the latest 3 consecutive years and if the year is the same, choose the title of the movie which comes first alphabetically. — user125535, Mar 10 '15 at 23:05
The point is to *update* the question. Not many read convoluted comments. The question should be presented in a clear fashion. And *always* add your version of Postgres. — Erwin Brandstetter, Mar 10 '15 at 23:15

Gareth Flowers · Accepted Answer · 2015-03-10T21:26:47.443

1

Use a Window Function, and select the first row in each id group:

SELECT *
FROM (
  SELECT *, ROW_NUMBER()
    OVER ( PARTITION BY id
      ORDER BY title1, title2, title3, year1 DESC, year2 DESC, year3 DESC ) AS row_number
  FROM foo
) x
WHERE x.row_number = 1;

edited Mar 10 '15 at 21:26

answered Mar 10 '15 at 20:52

Gareth Flowers

1,513
12
23

should the the highest 3 consecutive years so if ID 1 has year 1 = 2009 year 2 = 2010 year 3 =2011 and he also has another row which has year 1 = 2010, year 2 = 2011, year 3 =2012. It should choose the latter of the two rows. – user125535 Mar 10 '15 at 21:23
Missed that bit when I first read it! I've added a fix to sort the years descending, so that you'll get the largest first. – Gareth Flowers Mar 10 '15 at 21:27

score 0 · Answer 2 · edited May 23 '17 at 10:09

In Postgres, DISTINCT ON is typically simpler and faster:

SELECT DISTINCT ON (id) *
FROM   tbl
ORDER  BY id, title1, title2, title3, year1 DESC NULLS LAST;

Assuming that pick the title that comes first alphabetically means to order by title1 first, then title2, then title3.
Assuming that year1, year2 and year3 are always consecutive. So, sorting by either one of them does the trick.

NULLS LAST is only needed if year1 can be NULL:

PostgreSQL sort by datetime asc, null first?

Details for DISTINCT ON:

Select first row in each GROUP BY group?

how to select the row which comes first alphabetically and integer wise?

2 Answers2