1

I do have a table with one "type"-column, this column is a enum with eight possibilities. I now want to query the last row of each enum value - so if I'm looking for four of the eight possible values, I want four rows with each one of the enums.

Any ideas?

Edit examples: following enum: type=("example1","ex2","ex3") Each row has a type and a timestamp I'd like to have the last timestamp of every type.

Sample Data: example 1 - 7:13, ex2 - 8:14, example 1 - 9:17, ex3 - 6:13

Wished Result: example 1 - 9:17, ex2 - 8:14

Dialect is MySQL. Thanks!

Max Rln
  • 13
  • 5
  • 1
    This is a bit confusing. Could you include a few rows of sample data? – Bob Kaufman Apr 13 '17 at 17:36
  • 1
    Sample data and expected results would be helpful as well. – SS_DBA Apr 13 '17 at 17:38
  • Define "last". According to what order? Also, please specify database "dialect" (Oracle, MS SQL, MySQL, PostgreSQL, ...) because some have features making such a query easier, e.g. using windowing functions. – Andreas Apr 13 '17 at 17:39
  • Thanks - I implemented your suggestions - hope its clearer now – Max Rln Apr 13 '17 at 17:45
  • 1
    Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Andreas Apr 13 '17 at 19:44

1 Answers1

0

If your table MyTable has an "enum" column named EnumColumn, and you want last row for each value in a list of given enum values (Foo, Bar, and Baz), where "last" is defined by a DateColumn, and you're using a database that supports windowing functions (e.g. MS SQL Server, Oracle, PostgreSQL), you can do it like this:

SELECT Column1, EnumColumn, Column3, DateColumn, Column5
  FROM ( SELECT Column1, EnumColumn, Column3, DateColumn, Column5
              , ROW_NUMBER() OVER ( PARTITION BY EnumColumn
                                    ORDER BY DateColumn DESC ) AS ROW_NUM
           FROM MyTable
          WHERE EnumColumn IN ('Foo', 'Bar', 'Baz')
       ) x
 WHERE ROW_NUM = 1
Andreas
  • 154,647
  • 11
  • 152
  • 247