Mysql Query to fetch particular ID on the first position
0
SELECT `p`.`cat_pid`, `p`.`cat_id`, `c`.`name`, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY `p`.`cat_id` ASC, `p`.`created_date` ASC
And the response is:
> cat_pid cat_id Ascending 1 name images
3 6 LED TVs uploads/prod_file/0-1537255915-1.jpeg
3 7 Smart TVs uploads/prod_file/0-1537256346-1.jpg
3 13 Sony uploads/prod_file/3-1539672455-1.jpg
3 15 Digital SLRs uploads/prod_file/0-1539246776-1.jpg
1 21 T- shirt uploads/prod_file/0-1537179868-1.jpeg
1 22 Shirt uploads/prod_file/0-1542977731-1.png
1 23 Jeans uploads/prod_file/0-1539157883-1.jpeg
2 33 Ethnic Wear uploads/prod_file/4-1539757235-1.png, uploads/prod...
2 34 Earcuff Earrings uploads/prod_file/4-1539864784-1.jpg
2 36 Sarees uploads/prod_file/4-1540189359-1.jpg
38 39 Boy T shit uploads/prod_file/3-1539261170-1.jpg
4 43 Smartphones uploads/prod_file/0-1537183102-1.jpeg
4 45 Basic Mobiles uploads/prod_file/3-1539690488-1.jpg, uploads/prod...
38 50 CLOTHING SETS uploads/prod_file/3-1539253806-1.jpg, uploads/prod...
1 56 Backpacks uploads/prod_file/3-1539329576-1.jpg, uploads/prod...
1 57 Travel Luggage uploads/prod_file/0-1539330363-1.jpg
54 63 Cookware uploads/prod_file/4-1539934604-1.jpg
54 64 Gas stoves uploads/prod_file/0-1540185182-1.jpg
71 72 Ionizer uploads/prod_file/0-1543037560-1.png, uploads/prod..
Now I want cat_id 71 on first location rest of the data should be same As it is.
mysql
edited Nov 24 '18 at 6:59
fa06
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
|
show 1 more comment
0
SELECT `p`.`cat_pid`, `p`.`cat_id`, `c`.`name`, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY `p`.`cat_id` ASC, `p`.`created_date` ASC
And the response is:
> cat_pid cat_id Ascending 1 name images
3 6 LED TVs uploads/prod_file/0-1537255915-1.jpeg
3 7 Smart TVs uploads/prod_file/0-1537256346-1.jpg
3 13 Sony uploads/prod_file/3-1539672455-1.jpg
3 15 Digital SLRs uploads/prod_file/0-1539246776-1.jpg
1 21 T- shirt uploads/prod_file/0-1537179868-1.jpeg
1 22 Shirt uploads/prod_file/0-1542977731-1.png
1 23 Jeans uploads/prod_file/0-1539157883-1.jpeg
2 33 Ethnic Wear uploads/prod_file/4-1539757235-1.png, uploads/prod...
2 34 Earcuff Earrings uploads/prod_file/4-1539864784-1.jpg
2 36 Sarees uploads/prod_file/4-1540189359-1.jpg
38 39 Boy T shit uploads/prod_file/3-1539261170-1.jpg
4 43 Smartphones uploads/prod_file/0-1537183102-1.jpeg
4 45 Basic Mobiles uploads/prod_file/3-1539690488-1.jpg, uploads/prod...
38 50 CLOTHING SETS uploads/prod_file/3-1539253806-1.jpg, uploads/prod...
1 56 Backpacks uploads/prod_file/3-1539329576-1.jpg, uploads/prod...
1 57 Travel Luggage uploads/prod_file/0-1539330363-1.jpg
54 63 Cookware uploads/prod_file/4-1539934604-1.jpg
54 64 Gas stoves uploads/prod_file/0-1540185182-1.jpg
71 72 Ionizer uploads/prod_file/0-1543037560-1.png, uploads/prod..
Now I want cat_id 71 on first location rest of the data should be same As it is.
mysql
edited Nov 24 '18 at 6:59
fa06
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
1
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
1
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely usefetch_assocmethod. You can refer to the values using the key.
– Madhur Bhaiya
Nov 24 '18 at 7:03
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
1
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53
|
show 1 more comment
0
0
0
SELECT `p`.`cat_pid`, `p`.`cat_id`, `c`.`name`, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY `p`.`cat_id` ASC, `p`.`created_date` ASC
And the response is:
> cat_pid cat_id Ascending 1 name images
3 6 LED TVs uploads/prod_file/0-1537255915-1.jpeg
3 7 Smart TVs uploads/prod_file/0-1537256346-1.jpg
3 13 Sony uploads/prod_file/3-1539672455-1.jpg
3 15 Digital SLRs uploads/prod_file/0-1539246776-1.jpg
1 21 T- shirt uploads/prod_file/0-1537179868-1.jpeg
1 22 Shirt uploads/prod_file/0-1542977731-1.png
1 23 Jeans uploads/prod_file/0-1539157883-1.jpeg
2 33 Ethnic Wear uploads/prod_file/4-1539757235-1.png, uploads/prod...
2 34 Earcuff Earrings uploads/prod_file/4-1539864784-1.jpg
2 36 Sarees uploads/prod_file/4-1540189359-1.jpg
38 39 Boy T shit uploads/prod_file/3-1539261170-1.jpg
4 43 Smartphones uploads/prod_file/0-1537183102-1.jpeg
4 45 Basic Mobiles uploads/prod_file/3-1539690488-1.jpg, uploads/prod...
38 50 CLOTHING SETS uploads/prod_file/3-1539253806-1.jpg, uploads/prod...
1 56 Backpacks uploads/prod_file/3-1539329576-1.jpg, uploads/prod...
1 57 Travel Luggage uploads/prod_file/0-1539330363-1.jpg
54 63 Cookware uploads/prod_file/4-1539934604-1.jpg
54 64 Gas stoves uploads/prod_file/0-1540185182-1.jpg
71 72 Ionizer uploads/prod_file/0-1543037560-1.png, uploads/prod..
Now I want cat_id 71 on first location rest of the data should be same As it is.
mysql
edited Nov 24 '18 at 6:59
fa06
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
SELECT `p`.`cat_pid`, `p`.`cat_id`, `c`.`name`, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY `p`.`cat_id` ASC, `p`.`created_date` ASC
And the response is:
> cat_pid cat_id Ascending 1 name images
3 6 LED TVs uploads/prod_file/0-1537255915-1.jpeg
3 7 Smart TVs uploads/prod_file/0-1537256346-1.jpg
3 13 Sony uploads/prod_file/3-1539672455-1.jpg
3 15 Digital SLRs uploads/prod_file/0-1539246776-1.jpg
1 21 T- shirt uploads/prod_file/0-1537179868-1.jpeg
1 22 Shirt uploads/prod_file/0-1542977731-1.png
1 23 Jeans uploads/prod_file/0-1539157883-1.jpeg
2 33 Ethnic Wear uploads/prod_file/4-1539757235-1.png, uploads/prod...
2 34 Earcuff Earrings uploads/prod_file/4-1539864784-1.jpg
2 36 Sarees uploads/prod_file/4-1540189359-1.jpg
38 39 Boy T shit uploads/prod_file/3-1539261170-1.jpg
4 43 Smartphones uploads/prod_file/0-1537183102-1.jpeg
4 45 Basic Mobiles uploads/prod_file/3-1539690488-1.jpg, uploads/prod...
38 50 CLOTHING SETS uploads/prod_file/3-1539253806-1.jpg, uploads/prod...
1 56 Backpacks uploads/prod_file/3-1539329576-1.jpg, uploads/prod...
1 57 Travel Luggage uploads/prod_file/0-1539330363-1.jpg
54 63 Cookware uploads/prod_file/4-1539934604-1.jpg
54 64 Gas stoves uploads/prod_file/0-1540185182-1.jpg
71 72 Ionizer uploads/prod_file/0-1543037560-1.png, uploads/prod..
Now I want cat_id 71 on first location rest of the data should be same As it is.
mysql
mysql
edited Nov 24 '18 at 6:59
fa06
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
edited Nov 24 '18 at 6:59
fa06
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
edited Nov 24 '18 at 6:59
fa06
12.3k2917
edited Nov 24 '18 at 6:59
fa06
12.3k2917
edited Nov 24 '18 at 6:59
fa06
12.3k2917
12.3k2917
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
asked Nov 24 '18 at 6:58
Mukhpal SinghMukhpal Singh
264
264
1
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
1
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely usefetch_assocmethod. You can refer to the values using the key.
– Madhur Bhaiya
Nov 24 '18 at 7:03
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
1
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53
|
show 1 more comment
1
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
1
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely usefetch_assocmethod. You can refer to the values using the key.
– Madhur Bhaiya
Nov 24 '18 at 7:03
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
1
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53
1
1
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
1
1
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely use
fetch_assoc method. You can refer to the values using the key.– Madhur Bhaiya
Nov 24 '18 at 7:03
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely use
fetch_assoc method. You can refer to the values using the key.– Madhur Bhaiya
Nov 24 '18 at 7:03
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
1
1
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53
|
show 1 more comment
1 Answer
1
active
oldest
votes
2
You could use order by field()
SELECT `p`.`cat_pid`
, `p`.`cat_id`
, `c`.`name`
, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
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
});
}
});
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%2f53455923%2fmysql-query-to-fetch-particular-id-on-the-first-position%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
2
You could use order by field()
SELECT `p`.`cat_pid`
, `p`.`cat_id`
, `c`.`name`
, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
add a comment |
2
You could use order by field()
SELECT `p`.`cat_pid`
, `p`.`cat_id`
, `c`.`name`
, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
add a comment |
2
2
2
You could use order by field()
SELECT `p`.`cat_pid`
, `p`.`cat_id`
, `c`.`name`
, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
You could use order by field()
SELECT `p`.`cat_pid`
, `p`.`cat_id`
, `c`.`name`
, substring_index(group_concat(p.image_1 SEPARATOR ', '), ', ', 3) as images
FROM `products` `p`
LEFT JOIN `categories` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`admin_response` = 1
AND `p`.`isactive` = 1
GROUP BY `p`.`cat_id`
ORDER BY field(`p`.`cat_pid`, 71) DESC, `p`.`cat_pid` ASC, `p`.`created_date` ASC
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
answered Nov 24 '18 at 7:29
scaisEdgescaisEdge
92.5k104970
92.5k104970
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
add a comment |
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Hey, How i'll use this in CI query format '$this->db->order_by('p.cat_id,p.created_date', 'ASC');'
– Mukhpal Singh
Nov 24 '18 at 9:42
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
Thanks i've managed in framework.
– Mukhpal Singh
Nov 24 '18 at 9:58
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.
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%2f53455923%2fmysql-query-to-fetch-particular-id-on-the-first-position%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
1
Your ordering has no logic/pattern behind it. Why not simply do it in application code instead ?
– Madhur Bhaiya
Nov 24 '18 at 7:00
Ordering is working fine and data is also coming as per the expectation, I just need to locate Cat_id on first position.
– Mukhpal Singh
Nov 24 '18 at 7:01
1
Why does it even matter. If you are going to use this result-set in PHP code, you will most likely use
fetch_assocmethod. You can refer to the values using the key.– Madhur Bhaiya
Nov 24 '18 at 7:03
I have to create Rest API for this and 'return $query->result_array();' will use to create my result. So i don't to use any PHP code or loop here.
– Mukhpal Singh
Nov 24 '18 at 7:08
1
Possible duplicate of Mysql order by specific ID values
– Nigel Ren
Nov 24 '18 at 7:53