Running a query without GROUP BY and aggregation functions

Question

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

Answer 1

Is your syntax as strict as it seems there, or can distinct versus all be specified on a per-column basis? For instance, could you do something like this:

SELECT ship_date, DISTINCT item_id FROM tablename ORDER BY ship_date;

and if so, does that work?

If it doesn't, are you able to nest queries? (i.e. use a subquery in place of table_reference)

Answer 2

You just want the one item with the absolute latest ship_date? If so then your query is exactly right:

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

The only problem might be that if SHIP_DATE is something coarse (like a calendar date), rather than fine (like a datetime) then you could have ties. In that case, you database engine is just going to pick one of them and you won't know in advance how it will figure that out. So you might want to consider adding another column to your order by just to make it deterministic.

Answer 3

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.

Answer 4

select distinct item_id from item 
where ship_date = 
   (select top 1 ship_date from item order by ship_date desc)