StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORunning a query without GROUP BY and aggregation functions
text
Body
copied!<p>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):</p> <pre><code>{SQL query} ::= SELECT [DISTINCT | ALL] [TOP {integer}] {select_list} FROM {table_reference} [WHERE {search_condition}] [ORDER BY {orderby} { ',' {orderby} } ] </code></pre> <p>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?</p> <p>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?</p> <p>What I tried is something like this:</p> <pre><code>SELECT TOP N * FROM ITEM WHERE ITEM_ID='X' ORDER BY SHIP_DATE DESC </code></pre> <p>Actually the previous query seems to be working, but I'm wondering if there is a better way to do it.</p> <p>This is not homework, I need to create a query using the supported FileNet P8 syntax: <a href="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" rel="nofollow noreferrer">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</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload