get empty instead of repeated value in query
I have a table like this
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 2 | 1|efg | 1 |
| 3 | 1|cde | 1 |
| 4 | 2|zzz | 1 |
I want to run a query like this:
SELECT * FROM table WHERE prj=1 ORDER BY name
but printing out repeated values only once. I want to keep all the rows and I would like to do this at database level and not on the presentation layer (I know how to do it in php).
Desired result is
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 3 | |cde | 1 |
| 2 | |efg | 1 |
| 4 | 2|zzz | 1 |
any hint on where to start from to build that query?
mysql
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
add a comment |
I have a table like this
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 2 | 1|efg | 1 |
| 3 | 1|cde | 1 |
| 4 | 2|zzz | 1 |
I want to run a query like this:
SELECT * FROM table WHERE prj=1 ORDER BY name
but printing out repeated values only once. I want to keep all the rows and I would like to do this at database level and not on the presentation layer (I know how to do it in php).
Desired result is
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 3 | |cde | 1 |
| 2 | |efg | 1 |
| 4 | 2|zzz | 1 |
any hint on where to start from to build that query?
mysql
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
So, the row having lowestnumvalue for same values ofidwill be showing theidvalue; while the rest would shownull, right ?
– Madhur Bhaiya
Nov 15 '18 at 18:02
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07
add a comment |
I have a table like this
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 2 | 1|efg | 1 |
| 3 | 1|cde | 1 |
| 4 | 2|zzz | 1 |
I want to run a query like this:
SELECT * FROM table WHERE prj=1 ORDER BY name
but printing out repeated values only once. I want to keep all the rows and I would like to do this at database level and not on the presentation layer (I know how to do it in php).
Desired result is
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 3 | |cde | 1 |
| 2 | |efg | 1 |
| 4 | 2|zzz | 1 |
any hint on where to start from to build that query?
mysql
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
I have a table like this
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 2 | 1|efg | 1 |
| 3 | 1|cde | 1 |
| 4 | 2|zzz | 1 |
I want to run a query like this:
SELECT * FROM table WHERE prj=1 ORDER BY name
but printing out repeated values only once. I want to keep all the rows and I would like to do this at database level and not on the presentation layer (I know how to do it in php).
Desired result is
|num|id|name|prj|
| 1 | 1|abc | 1 |
| 3 | |cde | 1 |
| 2 | |efg | 1 |
| 4 | 2|zzz | 1 |
any hint on where to start from to build that query?
mysql
mysql
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
edited Nov 15 '18 at 18:06
Lelio Faieta
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
asked Nov 15 '18 at 17:57
Lelio FaietaLelio Faieta
4,13852447
4,13852447
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
So, the row having lowestnumvalue for same values ofidwill be showing theidvalue; while the rest would shownull, right ?
– Madhur Bhaiya
Nov 15 '18 at 18:02
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07
add a comment |
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
So, the row having lowestnumvalue for same values ofidwill be showing theidvalue; while the rest would shownull, right ?
– Madhur Bhaiya
Nov 15 '18 at 18:02
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
So, the row having lowest
num value for same values of id will be showing the id value; while the rest would show null, right ?– Madhur Bhaiya
Nov 15 '18 at 18:02
So, the row having lowest
num value for same values of id will be showing the id value; while the rest would show null, right ?– Madhur Bhaiya
Nov 15 '18 at 18:02
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07
add a comment |
1 Answer
1
active
oldest
votes
Use a session variable to test if the previous ID is the same as the current ID:
SELECT num, IF(@lastid = id, '', @lastid := id) AS id, name, prj
FROM table
CROSS JOIN (SELECT @lastid := null) x
ORDER BY table.id, name
DEMO
Note that you need to qualify table.id, because ORDER BY defaults to using the alias from the SELECT list if it's the same as a table column, and that would order the empty fields first.
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
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%2f53325369%2fget-empty-instead-of-repeated-value-in-query%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
Use a session variable to test if the previous ID is the same as the current ID:
SELECT num, IF(@lastid = id, '', @lastid := id) AS id, name, prj
FROM table
CROSS JOIN (SELECT @lastid := null) x
ORDER BY table.id, name
DEMO
Note that you need to qualify table.id, because ORDER BY defaults to using the alias from the SELECT list if it's the same as a table column, and that would order the empty fields first.
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
add a comment |
Use a session variable to test if the previous ID is the same as the current ID:
SELECT num, IF(@lastid = id, '', @lastid := id) AS id, name, prj
FROM table
CROSS JOIN (SELECT @lastid := null) x
ORDER BY table.id, name
DEMO
Note that you need to qualify table.id, because ORDER BY defaults to using the alias from the SELECT list if it's the same as a table column, and that would order the empty fields first.
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
add a comment |
Use a session variable to test if the previous ID is the same as the current ID:
SELECT num, IF(@lastid = id, '', @lastid := id) AS id, name, prj
FROM table
CROSS JOIN (SELECT @lastid := null) x
ORDER BY table.id, name
DEMO
Note that you need to qualify table.id, because ORDER BY defaults to using the alias from the SELECT list if it's the same as a table column, and that would order the empty fields first.
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
Use a session variable to test if the previous ID is the same as the current ID:
SELECT num, IF(@lastid = id, '', @lastid := id) AS id, name, prj
FROM table
CROSS JOIN (SELECT @lastid := null) x
ORDER BY table.id, name
DEMO
Note that you need to qualify table.id, because ORDER BY defaults to using the alias from the SELECT list if it's the same as a table column, and that would order the empty fields first.
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
edited Nov 15 '18 at 18:12
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
answered Nov 15 '18 at 18:07
BarmarBarmar
432k36255356
432k36255356
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
add a comment |
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 '18 at 18:10
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%2f53325369%2fget-empty-instead-of-repeated-value-in-query%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
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 '18 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 '18 at 18:01
So, the row having lowest
numvalue for same values ofidwill be showing theidvalue; while the rest would shownull, right ?– Madhur Bhaiya
Nov 15 '18 at 18:02
no, unfortunately that's not true. they will be ordered by "name" so the "num" can be mixed (if i add later something that in the ordering come first)
– Lelio Faieta
Nov 15 '18 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 '18 at 18:07