Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Unanswered: Select first(data) with Group by and Order by

    OH NO! More SQL woes

    I think I'm probably overlooking something really simple... pleeze help!

    I have a table, lets call it Sheet1, and it looks like this:
    Code:
    ID	Date	      value  quantity
    1	5/30/2008	52	7
    1	3/6/2010	36	5
    1	8/7/2009	346	78
    1	4/15/2001	46	26
    1	4/12/2003	23	37
    1	5/17/2009	6	4
    2	2/9/1999	74	478
    2	5/3/1997	46	234
    2	4/4/2004	568	47
    3	1/1/1988	267	56
    3	2/5/1977	54	54
    3	4/5/2003	37	5
    3	3/3/2003	54	54
    4	2/5/1995	45	67
    4	4/30/2010	34	23
    4	5/6/2007	34	23
    4	4/18/1988	46	24
    5	3/8/2008	23	54
    5	9/9/1979	123	56
    5	3/30/1998	135	39
    5	4/16/2003	57	462
    I am trying to write a query that gives me the FIRST value and LAST quantity sorted by date, for each individual ID.

    So, the result set I want is:
    Code:
    ID     firstOfValue   lastOfQuantity
    1         46                 5
    2         46                 47
    3         54                 5
    4         46                 23
    5         123                54
    So, I want to GROUP BY ID and ORDER BY Date.... but I can't figure it out.

    So far I have this SQL Query:
    Code:
    SELECT Sheet1.ID, First(Sheet1.value) AS FirstOfvalue, Last(Sheet1.quantity) AS LastOfquantity
    FROM Sheet1
    GROUP BY Sheet1.ID
    ORDER BY Sheet1.Date;
    But that gives the error "You tried to execute a blah blah that does not include Sheet1.Date as part of an aggregate function."

    But, if I include Date in the GROUP BY then I don't get 1 value for each ID, I get every single entry because the dates don't match....

    I've also tried:
    Code:
    SELECT Alias.ID, First(Alias.Date) AS FirstOfDate, First(Alias.value) AS FirstOfvalue, Last(Alias.quantity) AS LastOfquantity
    FROM (SELECT * FROM Sheet1
                ORDER BY Sheet1.ID, Sheet1.Date ASC
                 )  AS Alias
    GROUP BY Alias.ID;
    But, that doesn't work either

    Is this easier than I am making it?

    Thanks for reading

    Cheers!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Personally I might use a technique like this:

    Most recent value

    but with two base queries.
    Paul

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here is the same idea in one rather ugly query:

    Code:
    SELECT d.ID, SUM(d.myqty) AS LASTqty, SUM(d.myval) as FIRSTval
    FROM (
    		SELECT c.ID, c.myDate, c.qty AS myqty, 0 AS myval
    		FROM myTable AS c
    		RIGHT JOIN (
    					SELECT MAX(a.myDate) AS myDate, a.ID
    					FROM myTable AS a
    					GROUP BY a.ID
    				) AS b
    		ON b.myDate = c.myDate AND b.ID = c.ID
    	UNION ALL
    		SELECT c.ID, c.myDate, 0 AS myqty, c.val AS myval
    		FROM myTable AS c
    		RIGHT JOIN (
    					SELECT MIN(a.myDate) AS myDate, a.ID
    					FROM myTable AS a
    					GROUP BY a.ID
    				) AS b
    		ON b.myDate = c.myDate AND b.ID = c.ID
         ) AS d
    GROUP BY d.ID
    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Aug 2010
    Posts
    6
    Thanks for your responses

    Wow, so I guess it wasn't as easy as I thought it must be...

    This seems like a very common thing people would want to do.. why is it so complicated? :P

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you wanted the first and last date, it would be fairly simple. You want other values associated with those dates, which makes it a little trickier.
    Paul

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Have you considered using min and max, instead of first and last? First and last depends on position in the file; min and max doesn't care.

    SL

  7. #7
    Join Date
    Aug 2010
    Posts
    6
    Have you considered using min and max, instead of first and last? First and last depends on position in the file; min and max doesn't care.
    Well, min(value) would give me the actual minimum value for each ID rather than the earliest (in time) value...

    min(Date) works for giving me the earliest date..but then how would I get the value associated with that date? hmmmm

    That is of course without those huge ugly queries already suggested.. There must be a better way....

    Thanks for all your thoughts so far

  8. #8
    Join Date
    Aug 2010
    Posts
    6
    Code:
    SELECT Sheet1.*
    FROM Sheet1 INNER JOIN (SELECT Min(Sheet1.Date) AS MinDate, Sheet1.ID
         FROM Sheet1
         GROUP BY Sheet1.ID
    )  AS qryMinDates ON (Sheet1.Date = qryMinDates.MinDate) AND (Sheet1.ID = qryMinDates.ID)
    ORDER BY Sheet1.ID;
    Seems to work... It is 1 query... basically, I took the idea for 2 queries and just put it in 1...

    The only downside is that I cannot join on minDate AND maxDate.. which means I cannot get the min of value and max of quantity.. but alas.. its better than nothing

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    swap MAX/MIN & LAST/FIRST in my query (probably my LASTqty and FIRSTval is the opposite of what you wanted)
    edit my random choice of fieldnames to suit your reality.
    copy/paste the resulting ugliness and forget it.
    ugly is the query processor's problem, not yours.

    from the info you provided, i don't see a way out of the SUM(DATA JOIN MAX) UNION (DATA JOIN MIN) scenario. my guess is that "so far" is as far as it goes:

    izy

    PS you might want to consider avoiding reserved-word 'Date' as a field name.
    currently using SS 2008R2

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Like I said, I would have used 2 base queries and joined to both. In situations like this I've found I need the base queries for other things anyway.
    Paul

Posting Permissions

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