[Solved] ORDER BY problem with two columns

[computerbarry]

Hi all

What I’m trying to do is:

1. Return 5 results based on most HITS
2. Then ORDER BY DTSTAMP
3. Making sure to keep the most HITS in the LIMIT of 5

Sounds simply, but the problem is some records with the most HITS are not shown.

SELECT ID
  , SUMMARY
  , HITS
  FROM tbl_events
  WHERE DTSTAMP >= CURDATE()
  ORDER BY DTSTAMP ASC, HITS DESC
  LIMIT 5

I always want the most HITS to show but ORDER them by date.

Example
My most HITS record to date should be positioned at number 3, but with the code above, it’s not in the list.

Any ideas what I’m doing wrong?

Thanks, Barry

[r937]

SELECT * FROM ( SELECT id , summary , hits FROM tbl_events WHERE dtstamp >= CURRENT_DATE ORDER BY hits DESC LIMIT 5 ) AS d ORDER BY dtstamp ASC

[computerbarry]

Thanks r937

1054 - Unknown column ‘DTSTAMP’ in ‘order clause’

Update

Its working!

The error was showing inside my cpanel.
Once I updated things within my page/code and published, works great

And curious why using two SELECT - Is there a name for this technique?

Also wondering SELECT *
Will this slow things down eventually 1000’s of records will be selected.
Or not really an issue with this small query?

Anyhow, works good!

Cheers, Barry

[r937]

[quote=“computerbarry, post:3, topic:233961, full:true”]
And curious why using two SELECT - Is there a name for this technique?
[/quote]subquery

@computerbarry

Also wondering SELECT *

selecting from a subquery means that the only columns you can ask for are columns in the subquery’s SELECT clause (as you found out with dtstamp – sorry 'bout that, i should’ve spotted that)

@computerbarry

Will this slow things down eventually 1000’s of records will be selected.
Or not really an issue with this small query?

the SELECT * in the outer query has no impact on performance at all

performance of the subquery is the dealbreaker

[computerbarry]

@r937

subquery

Cool, yes makes sense now.

@r937

the SELECT * in the outer query has no impact on performance at all

Sound good to me, and nice to know

Ok, good result tonight!

One last question, if not, no bother.
But why did we need the subquery to accomplish this, get the result we needed?

Is the first SELECT * acting as an outer wrapper which we sort separately?

Thanks, Barry

[r937]

the subquery contains the LIMIT that gives you the top 5 rows based on hits

the outer query re-sorts the resulting 5 rows

[computerbarry]

Right on!

I thought something like this, makes sense now that you have explained and something I can use for future snippets now I understand.

Thanks for sharing the knowledge

Chat soon,
Barry