mysql help - sorting and counting

aleco

aleco

aleco

Associate
Joined
9 Jan 2006
Posts
114
I'm not that great with mysql commands, so was wondering if there's an easy way to do what I'm attempting:

I have a table like the following:

Code: 
id	item_no	user_id	timestamp
1	10001	5057	1263915032
2	10001	3	1263915088
3	10001	5057	1263917853
4	10001	5057	1263917863
5	10001	32	1263921113
6	10002	32	1263921555

And am wanting to perform a command where I exact all the data for a certain id, group it together by the item_no, count the number of items in this group, and have it group sorted so that the latest timetamp is fed back at the end of it, sorted by newest timestamp first. Like so:

(for all those IDs matching 10001):
Code: 
totals	item_no	user_id	timestamp 
1	10001	32	1263921113
3	10001	5057	1263917863
1	10001	3	1263915088

Any ideas?

edit: or would it be better/faster/less resource intensive to see if this can be done in php after just extracting the data in a normal way first?
 
Last edited:
Hmm not tested but try

Code: 
"SELECT DISTINCT totals, item_no, user_id, timestamp " .
"FROM table " .
"GROUP BY user_id " .
"ORDER BY timestamp DESC";

Could work!
 
Thanks for the reply but it's not quite it either!

The "totals" column listed in my 2nd example isn't in the database, I need it to count up all the instances and list it, and also this code gives the timestamp from the earliest entry of any multiple entries rather than the latest, e.g.:

it gives:
Code: 
10001	32	1263921113
10001	3	1263915088
10001	5057	1263915032

instead of
Code: 
10001	32	1263921113 
10001	5057	1263917863
10001	3	1263915088

and with counting/totals should be like:
Code: 
1 	 10001	32	1263921113 
3 	 10001	5057	1263917863
1 	 10001	3	1263915088
 
Code: 
SELECT
 COUNT(id) as totals
,MAX(item_no) as item_no
,MAX(user_id) as user_id
,MAX(timestamp) as timestamp
FROM `items`
WHERE `item_no` = 10001
GROUP BY `user_id`
ORDER BY MAX(timestamp)

Maybe?
 
Not sure if it helps, but the nearest thing I've got to it so far is:

Code: 
SELECT user_id, timestamp, count(user_id) AS total FROM table WHERE item_id = 10001  GROUP BY user_id WITH ROLLUP

which outputs:

Code: 
user_id	timestamp	total
3	1263915088	1
32	1263921113	1
5057	1263915032	3
NULL	1263915032	5

but as you can see, it gives the timestamp for the first occurrence of any multiple entries (i.e. "5057 1263915032 3" instead of "5057 1263917863 3", and should also be in the order of user_id 32 first, then 5057 next, and 3 last as they should be in timestamp order with the newest first.
 
Hmm that's just how I want it! It says: MySQL client version: 4.1.22, do I need to upgrade?
edit: actually not quite how I need it - it needs to then be sorted by timestamp too if poss!
 
It is sorted by timestamp? 126392111 < 1263915088 < 1263917863

4.1.22 is very old, it's from November 2006. I'm running 5.1.42 or something.
 
I'll see if I can upgrade (I presume it won't break backwards compatibility?), but the correct timestamp order should be:

1263921113 (5.11pm)
1263917863 (4.17pm)
1263915088 (3.31pm)

edit: ah sorry I must've deleted a 3 off 126392111 in the first place getting the formatting correct!
 
Shouldn't do. Just take a full backup of your databases first just incase.

Timestamp stores date and time, it sounds like you just need to use a time field instead?
 
Just backing up now and will perform the upgrade - will report back when done!

I need the date too for the timestamp, was just too lazy to type it out :o
 
I run my site on a VPS...but I don't really have any idea what I'm doing with it so just don't touch it unless I need to lol!

I was on php4 til last year so at least I'm up to date with both now! :o
 
You must log in or register to reply here.
Back
Top Bottom