Need "Select" help

Results 1 to 2 of 2

Thread: Need "Select" help

  1. #1
    SPG Guest

    Default Need "Select" help

    I&#039m just wondering if there&#039s any efficient way to get the following:<BR><BR>All values from table where distinct(value1) and maximum(value2 for each value1), order by value1.<BR><BR>This could track the most recent of a variety of events. For example, our latest shipments of Widgets, Wonkets and Foo could all be retrieved at the same time: <BR><BR>12/19/99 -- Widgets -- 10 crates<BR>12/23/99 -- Wonkets -- 3 crates<BR>4/2/00 -- Foo -- 98384 crates<BR><BR>without disrupting the older values (we shipped 101796 crates of Foo on 4/1/00 -- it&#039s a very popular product).<BR><BR>My initial instinct is to just grab the top 1 for each distinct value1, but that&#039s a Select for every value1 -- not very efficient.<BR><BR>Thanks for any insight.

  2. #2
    SPG Guest

    Default Solution found, nevermind.

    If you&#039re interested, it looks like this:<BR>(table1 has value1 as the primary key, table2 has value2-value1 pairs)<BR><BR>Select [columns] <BR>from [table1] inner join [table2] on [table1].value1 = [table2].value1<BR>where <BR><BR>value2 = (select max(value2) from [table2] where [table1].value1 = [table2].value1)<BR><BR>order by [table1].value1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts