selecting the next record from a list ordered by date.

Question

I am using this SQL query to order a list of records by date in a php page.

SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM table WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s');

This works fine.

In a different php page, I want to be able to delete this record, and show the next one in the list. The query I am using to do that is:

SELECT ARTICLE_NO FROM auctions1 WHERE str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) > (SELECT str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) FROM table WHERE ARTICLE_NO =".$pk.") ORDER BY str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ) LIMIT 1";

The problem I seem to be having, is because there are many records with the same date, any record from the group of records with the same date will be chosen, not the same one in the list.

How can I select the next record returned from the same result set as the first query? The first query always returns the same order, so I am not sure why the second query seems to have a different order.

edit:

I have been trying to use Quassnoi advice. The first query I am now using is:

SELECT  ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME,
        date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate
FROM    AUCTIONS1
WHERE   upper(ARTICLE_NAME) LIKE % x %
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no limit 0, 10

And the second query, as suggested by Quassnoi is:

SELECT  ARTICLE_NO
FROM    auctions1
WHERE   (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) >
        (
        SELECT  str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
        FROM    auctions1
        WHERE   ARTICLE_NO = xxx
        )
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
LIMIT 1

I copied this query by echoing it out through my php page, and simply placed xxx in place of the article_no that was present. This matches the first code example perfectly, however the results are the same as the code I was using in my original question.

edit2:

This is the query used to obtain the original result set:

SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME, date_format(str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), '%d %m %Y' ) AS shortDate FROM auctions1 WHERE upper(ARTICLE_NAME) LIKE '%o%' ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no limit 0, 10;

Which results in this data, which is fine:

ARTICLE_NO  USERNAME  ACCESSSTARTS   ARTICLE_NAME       shortDate
160288212077    5864_australen  30/09/2008 05:22:30  DON ED HARDY TIGER JACKE WEISS   XL    30 09 2008
220288566257    fashionticker1  01/10/2008 16:39:12  Ed Hardy Tank Top Lila Neu & OVP Gr. L    01 10 2008
280273115680    mulle15  01/10/2008 16:42:38  Ed Hardy, T-Shirt,Destroy, schwarz, Gr.L   01 10 2008
280273115991    mulle15  01/10/2008 16:43:54  Ed Hardy, T-Shirt,Destroy, schwarz, Gr.XL   01 10 2008
280273116224    mulle15  01/10/2008 16:44:59  Ed Hardy, T-Shirt,Destroy, schwarz, Gr.XXL   01 10 2008
280273118653    mulle15  01/10/2008 16:54:50  Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.M   01 10 2008
120312402767    lieschenjuli  01/10/2008 16:56:12  Badehose Shorts Ed Hardy L     01 10 2008
280273119206    mulle15  01/10/2008 16:56:47  Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.XL   01 10 2008
280273119489    mulle15  01/10/2008 16:57:49  Ed Hardy, T-Shirt,King Snoopy,chocolate, Gr.XXL  01 10 2008
160288777155    bonifatzius1  01/10/2008 16:58:33  Ed Hardy Bomberjacke Gr. L Jacke für Damen oder H...  01 10 2008

The problem is, if I perform this query:

SELECT  ARTICLE_NO
FROM    auctions1
WHERE   (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) >
        (
        SELECT  str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
        FROM    auctions1
        WHERE ARTICLE_NO =160288212077
        )
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
LIMIT 1;

The this returns 280273112610, when 220288566257 is what should be returned

Answer 1

You could try also ordering your results by ARTICLE_NO. Just do:

... ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), ARTICLE_NO ...

This way, both results sets should have exactly the same order. In both cases, the next record will be the record with the lowest ARTICLE_NO with a posterior date.

Greetings

Sacher

Answer 2

This is perfectly logic. You have a condition on ARTICLE_NAME in the first query that is missing from the second query - the second query returns MORE rows than the first one. Remove the LIMIT and you'll see. The second ordering condition is also a good idea, even if the number is arbitrary.

Answer 3

SELECT  ARTICLE_NO
FROM    auctions1
WHERE   upper(ARTICLE_NAME) LIKE '% x %'
        AND (str_to_date( ACCESSSTARTS, '%d/%m/%Y %k:%i:%s' ), article_no) >
        (
        SELECT  str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
        FROM    auctions1
        WHERE   ARTICLE_NO = @pk
        )
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no
LIMIT 1

Note that your original resultset:

SELECT  ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME
FROM    auctions1
WHERE   upper(ARTICLE_NAME) LIKE % x %
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s');

does not guarantee stable row order within one ACCESSSTARTS. You need to add PRIMARY KEY to the ORDER BY clause, like this:

SELECT  ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME
FROM    auctions1
WHERE   upper(ARTICLE_NAME) LIKE % x %
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'), article_no.

(I'm assuming ARTICLE_NO is the PRIMARY KEY of your table)

Your original rowset, depending on what access method it's using, returns rows either in table order in in index order.

You really, really, really need to change your original rowset to use the stable order.

But if you cannot do it for some reason, you can do the following:

SELECT  ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME,
FROM    (
        SELECT  @c := NULL
        ) vars,
        auctions1
WHERE   upper(ARTICLE_NAME) LIKE % x %
        AND CASE WHEN ARTICLE_NO = $PK THEN @с := 0 ELSE 0 END IS NOT NULL
        AND (@c := @c + 1) = 2
ORDER BY
        str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s');
LIMIT 1

This query is less efficient and heavily relies on fact that this query will use exactly same access method as your original query.

You don't normally rely on this fact, as access method can change anytime.

If you want clear and sane code, just add the ARTICLE_NO into ORDER BY and enjoy the query I posted first.