First and last row in MySQL database table for date and customer
up vote
0
down vote
favorite
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
mysql
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
add a comment |
up vote
0
down vote
favorite
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
mysql
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses aroundselectstatements inunion.
– Madhur Bhaiya
Nov 12 at 9:01
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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
mysql
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
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
mysql
mysql
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
asked Nov 12 at 8:56
Chevy Mark Sunderland
911211
911211
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses aroundselectstatements inunion.
– Madhur Bhaiya
Nov 12 at 9:01
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26
add a comment |
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses aroundselectstatements inunion.
– Madhur Bhaiya
Nov 12 at 9:01
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses around
select statements in union.– Madhur Bhaiya
Nov 12 at 9:01
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses around
select statements in union.– Madhur Bhaiya
Nov 12 at 9:01
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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...
answered Nov 12 at 9:26
Evgeni Enchev
1765
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258703%2ffirst-and-last-row-in-mysql-database-table-for-date-and-customer%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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...
answered Nov 12 at 9:26
Evgeni Enchev
1765
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
add a comment |
up vote
0
down vote
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...
answered Nov 12 at 9:26
Evgeni Enchev
1765
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
add a comment |
up vote
0
down vote
up vote
0
down vote
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...
answered Nov 12 at 9:26
Evgeni Enchev
1765
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...
answered Nov 12 at 9:26
Evgeni Enchev
1765
edited Nov 12 at 10:21
answered Nov 12 at 9:26
Evgeni Enchev
1765
answered Nov 12 at 9:26
Evgeni Enchev
1765
answered Nov 12 at 9:26
Evgeni Enchev
1765
1765
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
add a comment |
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
Thank you but the output not change and ORDER BY clause first UNION ALL syntax generate error [Err] 1221 - Incorrect usage of UNION and ORDER BY
– Chevy Mark Sunderland
Nov 12 at 9:47
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258703%2ffirst-and-last-row-in-mysql-database-table-for-date-and-customer%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please provide some sample data (preferably a DB/SQL Fiddle). You possible need parentheses around
selectstatements inunion.– Madhur Bhaiya
Nov 12 at 9:01
Move order by and limit to each individual query.
– Salman A
Nov 12 at 9:26