limit the records of a record-set

[bogan]

I have a table which looks like this:

CREATE TABLE `t_test` (
`T_REGION_ID` int(16) NOT NULL default '0',
`VALID_FROM` varchar(5) NOT NULL default '',
`TXT1` varchar(50) NOT NULL default '',
`DATE_CREATED` datetime NOT NULL default '0000-00-00 00:00:00'
) TYPE=MyISAM;



in the db there are always "7 record-sets" written for the same "DATE_CREATED", but each of those records contains a different "VALID_FROM" entry.

i.e.
(1, '10:00','text 1','2003-01-01 10:10:10');
(1, '10:30','text 2','2003-01-01 10:10:10');
(1, '11:00','text 3','2003-01-01 10:10:10');
(1, '11:30','text 4','2003-01-01 10:10:10');
(1, '12:00','text 5','2003-01-01 10:10:10');
(1, '12:30','text 6','2003-01-01 10:10:10');
(1, '13:00','text 7','2003-01-01 10:10:10');
(1, '20:00','text 1','2003-02-02 20:20:20');
(1, '20:30','text 2','2003-02-02 20:20:20');
(1, '21:00','text 3','2003-02-02 20:20:20');
(1, '21:30','text 4','2003-02-02 20:20:20');
(1, '22:00','text 5','2003-02-02 20:20:20');
(1, '22:30','text 6','2003-02-02 20:20:20');
(1, '23:00','text 7','2003-02-02 20:20:20');


how can i get now from each of those "7 record-sets" with the same DATE_CREATED only the first 4 records??

--> the result should look like this:
(1, '10:00','text 1','2003-01-01 10:10:10');
(1, '10:30','text 2','2003-01-01 10:10:10');
(1, '11:00','text 3','2003-01-01 10:10:10');
(1, '11:30','text 4','2003-01-01 10:10:10');
(1, '20:00','text 1','2003-02-02 20:20:20');
(1, '20:30','text 2','2003-02-02 20:20:20');
(1, '21:00','text 3','2003-02-02 20:20:20');
(1, '21:30','text 4','2003-02-02 20:20:20');


I just can't get it running... tryed with GROUP_BY, LIMIT, ... simply can't figure it out!

Anyone can help? Thanx!

 

[sleipnir214]

Check out the MySQL online documentation on the LIMIT clause:

http://www.mysql.com/doc/en/SELECT.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bogan]

thanx for the hint, sleipnir214, I did already check the documentation, and I know (believe) that I need to use the LIMIT function, but I still can't figure it out...

 

[sleipnir214]

LIMIT, in its most basic invocation, tells MySQL how many records, at max, to return from a query.

If the query "SELECT * FROM foo ORDER BY bar" will return 100 records, the query "SELECT * FROM foo ORDER BY bar LIMIT 10" will only return 10 records.

The more complex invocation can tell MySQL to return records 11 through 26 of a query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[bogan]

hi sleipnir214

ok, I got the meaning of the LIMIT function. BUT i still can't get it rigth for my specific SELECT-statement.

If I have this SELECT:

SELECT *
FROM `t_test`
WHERE T_REGION_ID = 1 AND DATE_CREATED
LIKE "2003-01-01%"
ORDER BY DATE_CREATED DESC , VALID_FROM ASC
LIMIT 4

then I get the first 4 records, all of "DATE_CREATED = 2003-01-01 10:10:10". so far so good. but (my fault, probably was not clear enough in my first question) on the same DAY of DATE_CREATED I have several "record-sets of 7 records", but each with a different TIME in the DATE_CREATED.

i.e.
I have for example 7 records (with same DATE_CREATED and different VALID_FROM) for the DAY 2003-01-01:

(1, '10:00','text 1','2003-01-01 10:10:10'); [7 records]
(1, '11:00','text 1','2003-01-01 11:11:11'); [7 records]
(1, '12:00','text 1','2003-01-01 12:12:12'); [7 records]

and I need to get only the first 4 of each of those record-sets of 7.

--> result:
(1, '10:00','text 1','2003-01-01 10:10:10'); [4 records]
(1, '11:00','text 1','2003-01-01 11:11:11'); [4 records]
(1, '12:00','text 1','2003-01-01 12:12:12'); [4 records]

hmm, is this clear enough now? is a little hard to explain...

thanx!

 

[DRJ478]

The layout of the table in your case makes handling it in one SQL statement impossible.
You will have to eihter retrieve all records, use PHP or another language to skip the records you don't need, or have a loop structure that gets 4 records at a time.

 

[bogan]

ok, thanx, "skipping records with php" was what i was thinking of as an other way of solving this problem, if i can't find the easy solution here with mysql.