next query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
paparucino
Forum Newbie
Posts: 4
Joined: Tue Nov 10, 2009 2:16 pm

next query

Post 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
 
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: next query

Post 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.
paparucino
Forum Newbie
Posts: 4
Joined: Tue Nov 10, 2009 2:16 pm

Re: next query

Post by paparucino »

The problem is that I dont know "name", I only know "foo"
User avatar
tr0gd0rr
Forum Contributor
Posts: 305
Joined: Thu May 11, 2006 8:58 pm
Location: Utah, USA

Re: next query

Post 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
paparucino
Forum Newbie
Posts: 4
Joined: Tue Nov 10, 2009 2:16 pm

Re: next query

Post by paparucino »

Sorry for the delay, had problems.
Your suggestion sounds good. I try it immediately

Thank you
Post Reply