Search code examples
sqlsyntaxfilenet-p8

Running a query without GROUP BY and aggregation functions

Let's say that you are limited to use only this syntax (you can't use aggregation functions like MAX or MIN, and neither you can't use GROUP BY clauses; please don't ask me why):

{SQL query} ::= SELECT [DISTINCT | ALL] [TOP {integer}]
                {select_list}
                FROM {table_reference}
                [WHERE {search_condition}]
                [ORDER BY {orderby} { ',' {orderby} } ]

Let's say that we have an ITEM table, where the identifier is called ITEM_ID. For a given ITEM_ID you could have many rows with the same ITEM_ID but different SHIP_DATE. How would you write a query to return only the ITEMS with the most recent SHIP_DATE given the previous syntax?

I already tried using TOP N (to retrieve the first row in the result set) combined with an ORDER BY (to sort from the max SHIP_DATE to the min SHIP_DATE). Any ideas or suggestions?

What I tried is something like this:

SELECT TOP N * FROM ITEM WHERE ITEM_ID='X' ORDER BY SHIP_DATE DESC

Actually the previous query seems to be working, but I'm wondering if there is a better way to do it.

This is not homework, I need to create a query using the supported FileNet P8 syntax: http://publib.boulder.ibm.com/infocenter/p8docs/v4r5m1/index.jsp?topic=/com.ibm.p8.doc/developer_help/content_engine_api/guide/query_sql_syntax_ref.htm

Solution

  • Assuming that ITEM.ship_date doesn't fall on the same date for any other ITEM record:

      SELECT TOP x
         i.item_id,
         i.ship_date
    FROM ITEM i 
ORDER BY i.ship_date DESC

    ...where x is the number of unique/distinct ITEM.item_id records. How'd you'd get that without being able to use COUNT...

    You have to use ORDER BY i.ship_date DESC in order to have the most recent ship_date records at the top of the result set.