• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Help with SQL Grouping

Hi..
I have the following table/data   I need to get the earliest sales date  by product.

PRODUCT        SALES           DATE
APPLES             20                 1/1/2010
BANANAS         5                  3/5/2009
APPLES            9                   2/9/2008
BANANAS            5                     11/29/2007
APPLES             4                   3/6/2007

THE RESULT SHOULD LOOK LIKE

APPLES     33   3/6/2007
BANANAS    10    11/29/2007

0
JElster
Asked:
JElster
1 Solution
 
Patrick MatthewsCommented:
SELECT Product, SUM(Sales) AS Sales, MIN([Date]) AS [Date]
FROM SomeTable
GROUP BY Product
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

SELECT * FROM products p WHERE date = (SELECT MIN(date) FROM products p1 WHERE p1.Product = p.Product )
0
 
JElsterAuthor Commented:
what's the dff between these two?  thanks
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Patrick MatthewsCommented:
Try them both, and I think the difference will be readily apparent :)
0
 
dshrivallabhCommented:
Try this

DECLARE @Result TABLE
(
      Product varchar(20),
      Sales int,
      Date Datetime
)

INSERT @REsult VALUES('APPLES',20,'1/1/2010')
INSERT @REsult VALUES('BANANAS',5,'3/5/2009')
INSERT @REsult VALUES('APPLES',5,'11/29/2007')
INSERT @REsult VALUES('BANANAS',20,'1/1/2010')
INSERT @REsult VALUES('APPLES',4,'3/6/2007')
         
Select Product, Sales,Date from @Result
Where Date IN (Select MIN(Date) FROM @Result GROUP BY Product)
0
 
Patrick MatthewsCommented:
dshrivallabh,

1) Your suggestion will not return the right answer.  Indeed, if the second-lowest date for Product B is the same as the lowest for Product A, then your query will return at least two rows for Product B

2) Please explain how your suggestion is materially different from http:#a30057641, posted more than 8 hours earlier than yours

Patrick
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now