Simple "order by" problem

[JohnnyDoomo]

Simple for you, but I am very new to just messing around with mysql functions.

 

I have 1 table that tracks both the item id and the amount of views the item has.

 

The id column is called gId, and the views column is called gplays.

 

All I am trying to do is get it to show a list of the latest 100 items, but sort them as most viewed.

 

I've looked at online tutorials to googling the function, but can't find anything that works.

 

Here's my current code:

$results = $db->cachegetall(300, "SELECT gId id, gName name, gDescription descr, gThumb thumb, gplays plays FROM games

        ORDER BY gId DESC LIMIT 100");

that just shows the latest 100.

 

When I try

        ORDER BY gId DESC, gplays DESC LIMIT 100");

I get nothing different. If I do

        ORDER BY gplays DESC LIMIT 100");

I get the 100 results, but they are from all items, and I want to only get it from the latest 100 items.

 

Can someone help me? I really have no idea what these functions are doing, other than the very basic tutorials I've read on the order by function.

[Frank_b]

why do you have all the columnnames double? (eg gId and id)

 

Anyway you will need a subquery like this:

SELECT
	*
FROM
	( SELECT * FROM items ORDER BY views DESC LIMIT 3 ) AS t1
ORDER BY
	name

Edited December 30, 2014 by Frank_b

[Frank_b]

What happens:

 

first the query between the brackets will be run. The result is the top 3 from the items table but sorted on their views score

second from the result that has been created we will SELECT all records but then sort them by name (alphabetical) which of course you can change to id.

 

any subquery must have his own alias in mysql, so i gave a random name t1.