Member Avatar for birdie612

I am an amateur when it comes to Advanced SQL but here is the situation.

I have a Test Table with data such as below:

Name(char) Qty(number) Date (char) Location (char)
Test1 1 11/14/08 10000 20050
Test1 1 11/14/08 10000 20100
Test1 1 11/14/08 10000 20000
Test1 1 11/15/08 10000 20200
Test1 1 11/14/08 10000 20020
Test2 1 11/14/08 10000 31000
Test2 1 11/16/08 10000 30300
Test2 1 11/3/08 10000 30020

I would like to combine the rows, sum up the qty, take the max date, and take the location of the max date row. The resulting view should look like the view below:

Name(char) Qty(number) Date (char) Location (char)
Test1 5 11/15/08 10000 20200
Test2 3 11/16/08 10000 30300

I run into the problem of getting the Location from the row with the Max date.

So far I have

SELECT Name,
       SUM("QTY") AS "Qty",
       MAX("Date") AS "Date",     
       "Location"
  FROM TEST_VIEW
  GROUP BY Name, Location;

But since there are different locations, it does not combine the rows. Any help would be appreciated.

  • 2 Contributors
  • 3 Replies
  • 108 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by birdie612
Member Avatar for dickersonka

add an order by date desc and the group by will select the top location

you said dates are chars though?

Member Avatar for birdie612

I believe the dates are chars because it is actually a full date/time like 11/14/2008 8:48:43 AM. I was looking into some nested select statements, but I will try your suggestion, thanks!

Member Avatar for birdie612

Ok, I figured out what to do, my solution is

SELECT NAME,
       (select SUM(QTY) AS QTY       
          FROM TEST_VIEW tv1
          WHERE tv1.NAME = tv.NAME
          GROUP BY NAME) AS QTY,
       LOCATION,     
       DATETIME
  FROM TEST_VIEW tv
  WHERE DATETIME in (SELECT MAX(DATETIME) from TEST_VIEW tv2 where tv2.NAME = tv.NAME )
  ORDER by NAME;

If instead my data came like this...

Name Qty DateTime Location
Test1/A 1 11/14/08 10000 20050
Test1/B 1 11/14/08 10000 20100
Test1/C 1 11/14/08 10000 20000
Test1/D 1 11/15/08 10000 20200
Test1/E 1 11/14/08 10000 20020
Test2/A 1 11/14/08 10000 31000
Test2/B 1 11/16/08 10000 30300
Test2/C 1 11/13/08 10000 30020

I want to get the same results...I know how to parse the name field, like below

SELECT SUBSTR(NAME, 0, INSTR(NAME,'/')-1) AS NAME,
       QTY,
       LOCATION,
       DATETIME       
FROM TEST_TABLE;

But I would like to parse the name column, and also do the stuff in the first SQL Statement (sum up qty and grab the latest datetime and location of that datetime row). Any ideas of merging the two SQL statements?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.