SQL buffs, lend a hand?

[gabrielcrowe]

I am struggling to create an SQL query:

I have a table, with product ID's and an update number

ID,freq
46,2
75,1
57,5
75,4
75,4

Clearly, in this example, product ID 75 is the most updated. however if i do:

SELECT DISTINCT ID, freq FROM table ORDER BY freq DESC

i get 57 as the winner. Why? becasue it has 5, and sql isnt adding up the 75's to get the true winner, at 9 views.

can anyone point me towards the right type of query?

[leehack]

You need to group by id and sum the frequency, or that's what i'd do in access for example, making sure frequency is a number of course.

SELECT Table.ID, SUM Table.FREQUENCY AS SumOfFREQUENCY FROM Table GROUP BY Table.ID would be access logic.

[gabrielcrowe]

lol, you edited.

I did:

SELECT ProdID, SUM(ViewNum) AS viewsum FROM actTrak GROUP BY ProdID ORDER BY viewsum DESC LIMIT 10

Thanks for pointing out my noobyness.

[leehack]

I did yes cos it looked plain wrong lol and i couldnt work out why, so i set up a quick table to try it.
I missed the group by off at the end.

75,9 was produced from it....eventually.

[gabrielcrowe]

FYI: i'm working on amazon style product suggestion.

they seem to have a magical way to tell what I want, and i want to know how!

[Darren B]

their spying on you gabe

[acompton]

they seem to have a magical way to tell what I want, and i want to know how!

Aren't they just feeding your recent purchases into "people who bought, also bought..."?

[gabrielcrowe]

no, since they can still give you the right items, even if you are not a member, nor have you purchased.

Example: amazon.co.uk
- Clear your cookies, and all that security stuff, clean slate
- I click call of duty 5 on the homepage and it shows me xbox games, all genres
- i click gears of war 2 and it learns i like shooters
- on the gears 2 page, it shows me a deal containing the game i just looked at the new new one, for less cash.
- the also bought list changes to reflect more shooty games, less others.
- i click fall out 3 and all the games in the similar items are shooters.

I did not log in.

now wherever i go, it shows me shooters. if i click on some hardware for the xbox, it starts to show me other hardware and shooters.

this intelligent similar items are adaptive and learning. This isnt a simple case of 'also bought'. This is more fun than that.

[Darren B]

Gabe i told ya, they are spying on you. Or using some clever hidden signal to read your brain patterns and make a psychological evaluation of your mental state and provide items to match

[acompton]

As a trader, I can tell you that when you add a product, Amazon let you identify the product category using a node 'tree' of categories. In my area, there aren't many nodes so I don't know how sophisticated their structure gets.

I still think they are using the 'also bought' list - its the easiest way to match 'tastes'. They are just comparing it with your product clicks.

Its possible they are mapping other peoples searches and clicks as well. (people who clicked on, also clicked on).

[gabrielcrowe]

The also bought list of other people, is a powerful tool, you're right.

i'll look into that, its actually an easy choice. But not very star trekky, is it?

[malbro]

now wherever i go, it shows me shooters. if i click on some hardware for the xbox, it starts to show me other hardware and shooters.

this intelligent similar items are adaptive and learning. This isnt a simple case of 'also bought'. This is more fun than that.

It looks like Amazon are beta test for Google see this recent article on personal searches http://www.webpronews/topnews/2008/1...ned-on-its-ear

[gabrielcrowe]

for those interested:
http://www.amazon.co.uk/gp/history/ref=pd_pyml_rhf

this is the amazon history of things you viewed.

they use this data, to build their pages specifically for you.

[grantglendinnin]

It looks like Amazon are beta test for Google see this recent article on personal searches http://www.webpronews/topnews/2008/1...ned-on-its-ear

Aww come on Malcolm, I didn't think anybody actually took Web Pro News seriously

[malbro]

Aww come on Malcolm, I didn't think anybody actually took Web Pro News seriously

Sign of the times