Post reply

Using "Top 1" and "Group By" Together

  • March 25, 2004 at 1:06 pm

    #152087

    How does one return results so that "Top 1" works within the "Group By" results?  I don't seem to have any luck.  Or do I need to use a subquery to achieve this?

    Thanks.

    --Lenard

  • March 25, 2004 at 2:46 pm

    #500497

    Got it sorted out with this article:

    http://www.sqlservercentral.com/columnists/sjones/findfirst.asp

    --Lenard

     

  • March 28, 2004 at 9:04 am

    #500811

    Lenard,

    I think, this is not the way relational tables or SQL are intended to be used.

    Here is a quote from the article you referred:

    "What we want is the first item in Natural order, or the order in which the items are stored".

    This concept of 'order in which the items are stored' belongs to a different area - that of sequential files or flat files.

    In relational tables, the order of storage is irrelevant. Your retrieval should not be based on the order of storage. The order might change if you unload and reload the data. If you need this functionality, you can achieve this by adding another column to the table.  

    You might want to consider this in your future database designs, if not in the current design.

    My two cents.

    R_achar

     

  • March 28, 2004 at 11:00 am

    #500816

    Grasshopper,

    What is wrong with displaying the "TOP 1" in a GROUP BY? In my case, an Event could be linked to multiple "event days".  I didn't want to show all the "event days" to the user.  Incase the event was composed of multiple "event days", I just needed way to summarize the event for the user to select for their activity.

    The code that helped me was:

    select 
id,nm,code
from
MyTest a
where
nm = ( select top 1 nm from MyTest b Where b.id = a.id )
  

    --Lenard 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply