Mysql Query to Group and Limit 1 Desc
Table name : users
I am trying to query this table such that it gives me a result showing - names for users - where - the last approved status - group by name - has manager_2 as Stan
Expected result image below
Something like
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE status = --- the last approved entry for (group by Name),
if manager_2 = stan
ORDER BY id DESC
LIMIT 1
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE manager_2 = 'stan' AND status = 'approved'
group by name
ORDER BY id DESC
LIMIT 1
None of the queries I tried are giving the expected results, infact I am finding it hard to even form a query to explain the request.
I guess, sub queries or joins is what i will have to go for, please suggest, joins is what I would prefer though.
mysql
edited Nov 15 '18 at 0:35
Barmar
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
add a comment |
Table name : users
I am trying to query this table such that it gives me a result showing - names for users - where - the last approved status - group by name - has manager_2 as Stan
Expected result image below
Something like
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE status = --- the last approved entry for (group by Name),
if manager_2 = stan
ORDER BY id DESC
LIMIT 1
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE manager_2 = 'stan' AND status = 'approved'
group by name
ORDER BY id DESC
LIMIT 1
None of the queries I tried are giving the expected results, infact I am finding it hard to even form a query to explain the request.
I guess, sub queries or joins is what i will have to go for, please suggest, joins is what I would prefer though.
mysql
edited Nov 15 '18 at 0:35
Barmar
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20
add a comment |
Table name : users
I am trying to query this table such that it gives me a result showing - names for users - where - the last approved status - group by name - has manager_2 as Stan
Expected result image below
Something like
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE status = --- the last approved entry for (group by Name),
if manager_2 = stan
ORDER BY id DESC
LIMIT 1
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE manager_2 = 'stan' AND status = 'approved'
group by name
ORDER BY id DESC
LIMIT 1
None of the queries I tried are giving the expected results, infact I am finding it hard to even form a query to explain the request.
I guess, sub queries or joins is what i will have to go for, please suggest, joins is what I would prefer though.
mysql
edited Nov 15 '18 at 0:35
Barmar
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
Table name : users
I am trying to query this table such that it gives me a result showing - names for users - where - the last approved status - group by name - has manager_2 as Stan
Expected result image below
Something like
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE status = --- the last approved entry for (group by Name),
if manager_2 = stan
ORDER BY id DESC
LIMIT 1
SELECT id,name,manager_1,manager_2,department_status_fruits
FROM users
WHERE manager_2 = 'stan' AND status = 'approved'
group by name
ORDER BY id DESC
LIMIT 1
None of the queries I tried are giving the expected results, infact I am finding it hard to even form a query to explain the request.
I guess, sub queries or joins is what i will have to go for, please suggest, joins is what I would prefer though.
mysql
mysql
edited Nov 15 '18 at 0:35
Barmar
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
edited Nov 15 '18 at 0:35
Barmar
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
edited Nov 15 '18 at 0:35
Barmar
429k36253352
edited Nov 15 '18 at 0:35
Barmar
429k36253352
edited Nov 15 '18 at 0:35
Barmar
429k36253352
429k36253352
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
asked Nov 15 '18 at 0:11
Manuraj SebastianManuraj Sebastian
597
597
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20
add a comment |
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20
add a comment |
1 Answer
1
active
oldest
votes
You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.
Then you can further filter this to those where manager_2 = 'stan'.
SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
FROM users
WHERE status = 'approved'
GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
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',
autoActivateHeartbeat: false,
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
);
);
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%2f53310658%2fmysql-query-to-group-and-limit-1-desc%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
You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.
Then you can further filter this to those where manager_2 = 'stan'.
SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
FROM users
WHERE status = 'approved'
GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
add a comment |
You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.
Then you can further filter this to those where manager_2 = 'stan'.
SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
FROM users
WHERE status = 'approved'
GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
add a comment |
You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.
Then you can further filter this to those where manager_2 = 'stan'.
SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
FROM users
WHERE status = 'approved'
GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
You need to use a query that returns the last approved row for each name. See SQL select only rows with max value on a column for various ways to do this.
Then you can further filter this to those where manager_2 = 'stan'.
SELECT u1.*
FROM users AS u1
JOIN (SELECT name, MAX(id) AS maxid
FROM users
WHERE status = 'approved'
GROUP BY name) AS u2 ON u1.id = u2.id
WHERE manager_2 = 'stan'
ORDER BY id DESC
LIMIT 1
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
answered Nov 15 '18 at 0:47
BarmarBarmar
429k36253352
429k36253352
add a comment |
add a comment |
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.
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%2f53310658%2fmysql-query-to-group-and-limit-1-desc%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
Unclear how you end up with just that single record.
– Tim Biegeleisen
Nov 15 '18 at 0:14
@Tim Biegeleisen have edited the details
– Manuraj Sebastian
Nov 15 '18 at 0:18
@Tim Biegeleisen , please refresh - now the details are clear
– Manuraj Sebastian
Nov 15 '18 at 0:20