Get the last 10 rows with order ascending

Question

I have tried all the existing attempts with a standard select query by nesting in another SELECT, then resorting with ASC, but it fails to load for some reason in my JSCharts. My code is working properly, minus the order:

SELECT `$tableName`.`$patient_idField` , `$tableDataName`.`$patient_idField` , `$tableDataName`.`$resultField` , `$tableName`.`$login_idField`
FROM `$tableName`
JOIN `$tableDataName` ON `$tableName`.`$patient_idField` = `$tableDataName`.`$patient_idField`
COLLATE utf8_general_ci
WHERE `$tableName`.`$login_idField` = $usssName
ORDER BY `$tableDataName`.id DESC LIMIT 10

Here is the attempt to re-order, that failed to load:

SELECT * FROM (
SELECT `$tableName`.`$patient_idField` , `$tableDataName`.`$patient_idField` , `$tableDataName`.`$resultField` , `$tableName`.`$login_idField`
FROM `$tableName`
JOIN `$tableDataName` ON `$tableName`.`$patient_idField` = `$tableDataName`.`$patient_idField`
COLLATE utf8_general_ci
WHERE `$tableName`.`$login_idField` = $usssName
ORDER BY `$tableDataName`.id DESC LIMIT 10 ) AS `$tableName` JOIN `$tableDataName` ORDER by `$tableDataName`.id ASC

UPDATED WITHOUT PHP...

SELECT userlist.patient_id, results.patient_id, results.result, userlist.login_id
FROM userlist
JOIN results ON userlist.patient_id = results.patient_id
COLLATE utf8_general_ci
WHERE userlist.login_id = ####
ORDER BY results.id DESC
LIMIT 10

Now if I perform anything outside of this to ASC it returns an error on duplicate of patient_id.

The code that worked, for future reference

SELECT  *
FROM (
SELECT userlist.patient_id, results.result, userlist.login_id, results.id
FROM userlist
  JOIN results ON userlist.patient_id = results.patient_id
COLLATE utf8_general_ci
WHERE userlist.login_id = ####
ORDER BY results.id DESC
LIMIT 10 ) temp
ORDER BY id

It involved removing one of the .patient_id on SELECT.

explain in a full sentence what you are trying to do? the title isn't clear enough — ysth, Jan 28 '13 at 23:20
_but it fails to load for some reason in my JSCharts_ - what happens when you run the failing query in MySQL directly? It is best to get your query working in isolation first, then plug it into other things. — halfer, Jan 28 '13 at 23:45
I suspect the subselect is getting a Duplicate column name error (given that there are two `$patient_idField` columns being returned) — ysth, Jan 28 '13 at 23:50
@ysth no, absolutely use databases, but use only basic queries, dump the data into php arrays, and do all the logic in php. it sounds like you're new to databases, so i recommend you 1) look into what makes a database fast, and 2) do all of your queries off of the `PRIMARY` — , Jan 29 '13 at 00:19
Please do not dirty delete. Let the post be present for others to understand the confusion. — Ravi Y, Jan 29 '13 at 15:31
I don't think it's fair to receive help and then remove the question afterwards. See the FAQ about using this site only if you are happy for your questions and answers to be edited. Don't worry about the odd downvote - they happen to nearly everyone, and it's being helped that counts. — halfer, Jan 29 '13 at 15:40
Please do not vandalize your content. We're not going to remove a question with quality answers, because that would penalize those who took the time to answer you. Also, it's not very polite to accuse people who tried to help you out of gaming the system. I see no evidence of that here at all. — Brad Larson, Jan 29 '13 at 17:34
@Shane - I just told you not to deface your posts, so I'm rolling this back again. I'm not sure why you're reacting so strongly to people making minor improvements to your question, or to someone providing a legitimate answer to your question. I'm telling you, there are no shenanigans here, and ysth is a trusted user of the site. You don't get to 41,000 reputation points by cheating, you do so by leaving 1,100 good answers. I'm locking this to give you some time to cool down. — Brad Larson, Jan 29 '13 at 18:10
Hi @Shane - there's no point in getting into an edit war with a moderator, since it could lead, eventually, to suspension. In general I am fine with my posts being edited - I [had one recently](http://stackoverflow.com/a/9570081/472495) in which I posted deprecated information, and someone kindly fixed it. Whilst your question was a good one, an ongoing dispute risks attracting further downvotes - the rules about accepting third-party edits are very established here, and aren't going to change. As I imply, I think we should support them. — halfer, Jan 29 '13 at 21:16
(For the historical record, the OP appears to have deleted their comments from the question and from conversions under answers). Downvoted. — halfer, Jan 30 '13 at 16:56
and then voted to close their own question, since they weren't allowed to wipe it? — ysth, Jan 30 '13 at 21:54
Gotta love Stackoverflow. Even if I want to remove myself and activity from a odd conversation and group of vote hunters, I am penalized for it. I use this site to gain info and it is odd to deal with those who want to attack (down-vote) and throw passive aggression when I don't give them or their friend the green check mark. "For historical record"... wow! Let's get back to earth my friends, we are talking about an issue with JOIN and ASC. — Shane, Feb 01 '13 at 08:28
continued... Btw, I put that important part of the problem in my header and it is stripped out so ysth can say "explain in a full sentence what you are trying to do? the title isn't clear enough" and get up-voted for it. Maybe change my post to "I have a question" so it sounds even more ridiculous. — Shane, Feb 01 '13 at 08:29

score 2 · Answer 1 · answered Jan 28 '13 at 23:38

2

Nesting in another select should work; how did it fail?

Something like:

select tablenamepatientidfield, tabledatanamepatientidfield, resultfield, loginidfield from (
    SELECT
        `$tableDataName`.id,
        `$tableName`.`$patient_idField` AS tablenamepatientidfield,
        `$tableDataName`.`$patient_idField` AS tabledatanamepatientidfield,
        `$tableDataName`.`$resultField` AS resultfield,
        `$tableName`.`$login_idField` AS loginidfield,
    FROM `$tableName`
    JOIN `$tableDataName` ON `$tableName`.`$patient_idField` = `$tableDataName`.`$patient_idField`
    COLLATE utf8_general_ci
    WHERE `$tableName`.`$login_idField` = $usssName
    ORDER BY `$tableDataName`.id DESC LIMIT 10
) AS descending_select ORDER BY id;

(It is a little awkward that the column names are all variable and could collide (and don't include id itself).)

answered Jan 28 '13 at 23:38

ysth

96,171
6
121
214

@Shane - you'll need to do some debugging, then. Will this run on your MySQL console? Try it. – halfer Jan 28 '13 at 23:55
1

err, that's why I used two different AS aliases for them – ysth Jan 29 '13 at 08:28
@Shane - suspect ysth got upvotes from the community, as is usual on this site. The answer certainly looks right... – halfer Jan 29 '13 at 15:46
1

well, all I can say is that the problem you report having with my solution isn't possible, so I suspect you didn't try exactly what I suggested. I certainly may have an error or a typo leading to some other error, though. – ysth Jan 29 '13 at 18:19
1

I have no interested in discussing that; I wish you would respond to what I am saying though. – ysth Jan 29 '13 at 18:31
Alterations? What? I think you are confusing me and Lance; Lance's original answer failed because of the duplicate patient id; it's why I bothered answering in the first place. – ysth Jan 29 '13 at 20:52

Lance · Accepted Answer · 2013-01-29T00:21:05.210

1

Can you just do this

select * from (
    SELECT `$tableName`.`$patient_idField` 
         , `$tableDataName`.`$patient_idField` 
         , `$tableDataName`.`$resultField` 
         , `$tableName`.`$login_idField`
         ,`$tableDataName`.id
    FROM `$tableName`
      JOIN `$tableDataName` ON `$tableName`.`$patient_idField` 
                             = `$tableDataName`.`$patient_idField`
    COLLATE utf8_general_ci
    WHERE `$tableName`.`$login_idField` = $usssName
    ORDER BY `$tableDataName`.id DESC LIMIT 10 ) t
order by id

try this, I have edited to better reflect what you are actually doing

select  *
from (
    SELECT userlist.patient_id, results.result, userlist.login_id
    FROM userlist
      JOIN results ON userlist.patient_id = results.patient_id
    COLLATE utf8_general_ci
    WHERE userlist.login_id = ####
    ORDER BY results.id DESC
    LIMIT 10 ) temp
order by patient_id

edited Jan 29 '13 at 00:21

answered Jan 28 '13 at 23:37

Lance

3,193
2
32
49

Interesting I can get it to run in an analyzer without any issue. Can you tell me the error message? – Lance Jan 29 '13 at 00:01
You are outputing the ID twice (userlist.patient_id, results.patient_id) the are the same since you are joining on them so you only need to output on or at least alias the second one (userlist.patient_id, results.patient_id as secondIdOutput) – Lance Jan 29 '13 at 00:18
see the 2nd query in my answer above – Lance Jan 29 '13 at 00:21
1

This was the first answer to work and updates by others did not improve on this answer as far as I can tell.. so this is the correct answer "for Historical purposes" lol. Now if only I could have dealt with people like Lance, this would have allowed Stackoverflow to work the way it was designed. Unfortunately though, I came across those (ysth and halfer) who want to write about how to use stackoverflow and be self-proclaimed experts on how this should work and who I should choose as my answer. – Shane Feb 06 '13 at 08:35
1

@Shane Sorry that this post became painful. I agree that sometimes the "community" get out of hand. If you don't like a post just ignore it, right. With this said, glad I was able to help – Lance Feb 06 '13 at 16:35

Get the last 10 rows with order ascending

The code that worked, for future reference

2 Answers2