SQL, MYSQL, PHP question

[IHateMyJob2004]

I'm the newbie that asked about dev yesterday. As a recap, I am working on a web page using MYSQL server and PHP.

I'm making progress but have a complex query that I need to do and have no idea where to start.

<B>THE DATA</B>
Imagine the following table:
String,int,float are the data types in that order
AA 2000 19.1
AA 2001 15.1
AA 2002 19.0
AA 2003 19.4
AA 2004 19.2
AA 2005 12.1
AA 2006 19.2
AA 2007 12.1
AA 2008 19.2
AA 2009 12.1

BB 2000 19.1
BB 2001 15.1
BB 2002 19.0
BB 2003 19.4
BB 2004 19.2
BB 2005 12.1
BB 2006 19.2
BB 2007 12.1
BB 2008 19.2

Each row has more than 3 pieces of data. More like 50.

And this is stock data. So, ticker, year, value. Thousands of stocks so we're talking a ROM (rough order of magnitude) of 50,000 rows of data.


<B>THE QUERY</B>
Now for the fun. Note that the last year of data that is available is not always the same. BB ends in 2008 while AA ends in 2009. (It gets more fun, CC might only have data for 2008 and 2009 if it's a new stock. Let's ignore that for now unless it is easy to deal with.)

So, I need to get the total for the last 3 years values. How do I do that? For a return value, I really only need the ticker and the 3 year total.

Ya, I'm new to this. Any help is appreciated more than you can imagine.

 

[Crusty]

Just ORDER BY date DESC and LIMIT 3 your query to get the latest 3 years.

Do you have any sort of working SQL for it yet?

 

[IHateMyJob2004]

At this point, I have enough SQL to pull data out and understand the table structure.

Will doing Limit give me the years 2007-2009 for AA and 2006-2008 for BB? If I can get that data, that is a big first step that could be used in a nested query.

So, select * from table_name order by date desc limit 3
???

Wouldn't that chop off 2006 for BB?

Ideally, if I could get the row data for the following, that would be ideal:
AA 2007 12.1
AA 2008 19.2
AA 2009 12.1

BB 2006 19.2
BB 2007 12.1
BB 2008 19.2

 

[Crusty]

If you are trying to get all symbols listed like that out of the table you're going to either need to do a lot of unions or a lot of joins. I would suggest using a loop in your php to iterate the symbols available and query the data table like

SELECT year, price, other_field FROM data WHERE symbol = 'AA' ORDER BY year DESC LIMIT 3

 

[IHateMyJob2004]

It sounds like I need something like this:
SELECT stock, MAX(date) as "xxx"
FROM table_name
GROUP BY stock;

 

[IHateMyJob2004]

If you are trying to get all symbols listed like that out of the table you're going to either need to do a lot of unions or a lot of joins. I would suggest using a loop in your php to iterate the symbols available and query the data table like

SELECT year, price, other_field FROM data WHERE symbol = 'AA' ORDER BY year DESC LIMIT 3

I'll try this out and see what performance is like. I'd like to optimize things but that can come later.

EDIT: Wouldn't doing so many queries result in a performance issue?

 

[GodlessAstronomer]

Google "SQL union". Forget about performance issues for now - even experienced developers are notoriously bad at identifying performance issues ahead of time.

 

[PhatoseAlpha]

Wouldn't it be simpler to do it as two queries and a join?

Query A: Select Code, MAX(YEAR) as MaxYear from Table Group by Code;
Query B: Select Table.* from Table, QueryA where Table.Code = QueryA.code and Table.Year >= QueryA.MaxYear-2;

 

[IHateMyJob2004]

Wouldn't it be simpler to do it as two queries and a join?

Query A: Select Code, MAX(YEAR) as MaxYear from Table Group by Code;
Query B: Select Table.* from Table, QueryA where Table.Code = QueryA.code and Table.Year >= QueryA.MaxYear-2;

Thanks alot. I did this earlier today and it worked great.

 

[IHateMyJob2004]

PhatosAlpha (or anyone),

Your solution worked. I had it working but at another location but written as a single query (Using aliases). And I can not get it working again with aliases.

Could you or another tell me what to do to do this as a single line query?

EDIT: Never mind .... I got it. Something just clicked in my head. I'll post the query upon request.

 

[JasonCoder]

Not sure why a UNION is being discussed. Sounds like you need to group on the ticker, sorted by ticker, filtered by date...

SELECT sum(column_float) AS Amount, WHERE column_date >= some_year ORDER BY column_date