CODEWITHSUNDEEP
mysql
Chevy Mark Sunderland
Chevy Mark Sunderland

Reputation: 435

First and last row in MySQL database table for date and customer

With this SQL query on MySQL database table I have selected the first access on my application web for current date and for customer :

mysql> SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    t.myDate,
    t.myCustomer
FROM
    tbl_new AS t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE
    t.myDate IN (CURDATE())
ORDER BY
    t.myDate DESC
LIMIT 1;
+-----------+------------+------------+
| rowNumber | myDate     | myCustomer |
+-----------+------------+------------+
|         1 | 2018-11-12 | 561731A    |
+-----------+------------+------------+
1 row in set

With this SQL query on MySQL database table I have selected the last access on my webpage for current date and for customer :

mysql> SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    myDate,
    myCustomer
FROM
    tbl_new
ORDER BY
    myDate DESC
LIMIT 1;
+-----------+------------+------------+
| rowNumber | myDate     | myCustomer |
+-----------+------------+------------+
|         2 | 2018-11-12 | 719020A    |
+-----------+------------+------------+
1 row in set

Now I need UNION ALL these SQL queries for unique output, but the output of last access is wrong 908324A instead of 719020A,

Why ?

mysql> SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    t.myDate,
    t.myCustomer
FROM
    tbl_new AS t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE
    t.myDate IN (CURDATE())
UNION ALL
    SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    myDate,
    myCustomer
FROM
    tbl_new
ORDER BY
    myDate DESC
    LIMIT 2;
+-----------+------------+------------+
| rowNumber | myData     | myCustomer |
+-----------+------------+------------+
|    246616 | 2018-11-12 | 561731A    |
|    111872 | 2018-11-12 | 908324A    |
+-----------+------------+------------+
2 rows in set

Upvotes: 1

Views: 50

Answers (1)

Evgeni Enchev
Evgeni Enchev

Reputation: 552

I'd say your sql should be like this:

SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    t.myDate,
    t.myCustomer
FROM
    tbl_new AS t
CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE
    t.myDate IN (CURDATE())
ORDER BY
    t.myDate DESC
LIMIT 1
UNION ALL
SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    myDate,
    myCustomer
FROM
    tbl_new
ORDER BY
    myDate DESC
LIMIT 1

Just your two sentences with UNON ALL.

EDITED

I cannot post comments. May be you can try putting the queries in parenthesis:

SELECT * FROM (
SELECT (@cnt := @cnt + 1) AS rowNumber,
...
ORDER BY ...
)
UNION ALL
SELECT * FROM ( ... )

Not sure, if you can provide a fiddle...

Upvotes: 1

Related Questions