Page 1 of 1

next query

Posted: Tue Dec 08, 2009 2:46 pm
by paparucino
Hi all,
I need help on performing queries. Try to explain.
Ive a db_table with following fields: date, type, name.. where name is a subgroup of type.
On 01/01/200x there are some data so I can start from there to do my search an operations.
Then during the year some new name of the same type as the one of already presnt records, is inserted in db_table and from that point on there are some records with those characteristics.
What I cant do is to find the "start point" of each new name.

Hope this is more clear :-)

Code: Select all

date | type | name 
01-01| foo  | AA
01-01| foo  | AB
01-01! foo  | AC
02-01| foo  | AA
02-01| foo  | AB
02-01! foo  | AC
[...]
05-02| foo  | AA
05-02| foo  | AB
05-02! foo  | AC
[color=#FF0000][b]05-02| foo  | BA[/b][/color]
07-02| foo  | AA
07-02| foo  | AB
07-02! foo  | AC
07-02| foo  | BA
[...]
15-03| foo  | AA
15-03| foo  | AB
15-03! foo  | AC
15-03| foo  | BA
[color=#4080FF][b]15-03| foo  | CA[/b][/color]
15-03| foo  | AA
15-03| foo  | AB
15-03! foo  | AC
15-03| foo  | BA
15-03| foo  | CA
 

Re: next query

Posted: Wed Dec 09, 2009 2:49 pm
by tr0gd0rr
You may be looking for a query that uses MIN(date) like the following:

Code: Select all

SELECT MIN(`date`)
FROM db_table
WHERE `type` = 'foo' AND `name` = 'BA'
This query will tell you the earliest date out of all records with type 'foo' and name 'BA'.

By the way, keep in mind that database rows do not have "an order". The order of insert may not be the order of results returned in a 'SELECT * FROM table' query. Also be careful with naming columns "date", "type" and "name": I think those are all reserved words in MySQL so you may need to surround those in back ticks to avoid parse errors and unexpected results.

Re: next query

Posted: Thu Dec 10, 2009 7:53 am
by paparucino
The problem is that I dont know "name", I only know "foo"

Re: next query

Posted: Thu Dec 10, 2009 9:20 am
by tr0gd0rr
In that case, you could do a group by:

Code: Select all

SELECT MIN(`date`) AS earliest, `name`
FROM db_table
WHERE `type` = 'foo'
GROUP BY `name`
ORDER BY earliest
Results should be:

Code: Select all

earliest| name
01-01   | AA
01-01   | AB
01-01   | AC
05-02   | BA
15-03   | CA

Re: next query

Posted: Wed Dec 23, 2009 7:15 am
by paparucino
Sorry for the delay, had problems.
Your suggestion sounds good. I try it immediately

Thank you