get empty instead of repeated value in query
up vote
0
down vote
favorite
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 at 17:57
Lelio Faieta
4,05552345
add a comment |
up vote
0
down vote
favorite
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 at 17:57
Lelio Faieta
4,05552345
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 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 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 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 17:57
Lelio Faieta
4,05552345
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 at 17:57
Lelio Faieta
4,05552345
asked Nov 15 at 17:57
Lelio Faieta
4,05552345
edited Nov 15 at 18:06
asked Nov 15 at 17:57
Lelio Faieta
4,05552345
asked Nov 15 at 17:57
Lelio Faieta
4,05552345
asked Nov 15 at 17:57
Lelio Faieta
4,05552345
4,05552345
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 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 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 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07
add a comment |
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 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 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 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 at 18:00
Is there a PK in your table ?
– Madhur Bhaiya
Nov 15 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 at 18:01
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 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 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 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 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 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
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 at 18:07
Barmar
416k34240341
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 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',
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
up vote
2
down vote
accepted
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 at 18:07
Barmar
416k34240341
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 at 18:10
add a comment |
up vote
2
down vote
accepted
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 at 18:07
Barmar
416k34240341
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 at 18:10
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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 at 18:07
Barmar
416k34240341
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 at 18:07
Barmar
416k34240341
edited Nov 15 at 18:12
answered Nov 15 at 18:07
Barmar
416k34240341
answered Nov 15 at 18:07
Barmar
416k34240341
answered Nov 15 at 18:07
Barmar
416k34240341
416k34240341
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 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 at 18:10
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 at 18:10
very smart! That's what I was trying to get! Thank you for the help, Barmar!
– Lelio Faieta
Nov 15 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.
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.
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 at 18:00
yes there is a pk that is an autoincrement value and is called "num" (not shown here)
– Lelio Faieta
Nov 15 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 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 at 18:04
@MadhurBhaiya I have edited the question to reflect what you are asking
– Lelio Faieta
Nov 15 at 18:07