Computing desk | ||
---|---|---|
< February 8 | << Jan | February | Mar >> | February 10 > |
Welcome to the Wikipedia Computing Reference Desk Archives |
---|
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
I am writing a system which shows users' recently played songs. When a user listens to a song, details of this song are sent to my website which then inserts the artist, song, time and username into a MySQL database table. What I want to do, however, is show the 10 most listened to songs in addition to the most recent songs. I've got the most recent songs query working absolutely fine but I am facing problems with getting a list of the most played songs.
The table schema is as follows:
listenid - int(10) unsigned auto-increment key (the key row)
artist - varchar(255) (the name of the song's artist)
song - varchar(255) (the song name)
fulltext - char(64) (the artist and song name concatenated and run through MD5 cyphering - I thought this would help for counting rows since only one column needs counting as opposed to two, which I don't think is supported)
time - int(10) unsigned (the UNIX timestamp for the time when the song was submitted to the site)
Basically, I need a list of the number of rows with matching song name and artist, ordered descending. I tried using the following to no avail:
SELECT artist, song, COUNT(fulltext) AS times FROM nowplaying GROUP BY fulltext LIMIT 10
That query returns an error message saying there is a problem with the syntax near 'fulltext)'. Any ideas? RevenDS 01:35, 9 February 2007 (UTC)
See if this works:
SELECT MIN(artist), MIN(song), COUNT(fulltext) AS times FROM nowplaying GROUP BY fulltext LIMIT 10
— EncMstr 01:43, 9 February 2007 (UTC)
SELECT artist,song,COUNT(*) times FROM nowplaying GROUP BY artist,song ORDER BY times DESC LIMIT 10
@ 2007-02-09T03:26Z
AS times
, and so GROUP BY
should refer to the same name, i.e. GROUP BY times
instead of GROUP BY fulltext
. If AS times
was omitted, you should use GROUP BY COUNT(fulltext)
. Feel free to correct if I'm wrong. —
Xhantar
Talk 07:26, 9 February 2007 (UTC)
group by count(*)
wouldn't give a useful result. Group by
doesn't have to reference a field which appears in the select_list. COUNT(*) AS times
creates a new variable, so to speak. I had trouble escaping fulltext to prevent MySQL from using it as a keyword: and that's what the errors I got seemed to be pointing to. —
EncMstr 07:40, 9 February 2007 (UTC)@ 2007-02-09T07:38Z
GROUP BY
on multiple fields. For clarity, I was refering to this; this is the SQL that will successfully retrieve a list of the most-mentioned artist/song combination:SELECT artist, song, COUNT(*) AS times FROM nowplaying GROUP BY artist, song ORDER BY times DESC LIMIT 10
I have two SATA 160 GB drives that I would like to arrange such that there are three small RAID 0 partitions for WinXP, OpenSUSE and Ubuntu and a large RAID 1 partition(s) for important data. Is it possible to have the same drives set up like this? Is it complicated? -- Seans Potato Business 06:11, 9 February 2007 (UTC)
Is there freeware for website design, best if it is WYSIWYG (probably) ... that I can use to design, test, and demonstrate my website without actually publishing it to the web on a server? My 'site', I suppose, would be a file that could be viewed by my browser (or I can send the file to someone else who could view it on their browser...), as though I were online, but without actually being online where my incomplete or confidential information might be accessed. (...hate sounding like such a rookie, but you go to war with the army you have, so to speak...)
I have an
X.509 certificate which is being used on our local mail server. I want to sign this certificate with my
OpenPGP key so that someone who trusts me trusts me will trust the server. I have an inkling that this is possible (we're using
Enigmail with
Mozilla Thunderbird), but I have no idea how to go about it. Is it worth doing? Is it possible? I fetched the cert with openssl s_client -connect myserver:993
, and I have it in
ASCII armored format. I'm using
GPG to manage my OpenPGP keys.
grendel|
khan 16:47, 9 February 2007 (UTC)
Where can I find a FREE program that tapes/records streaming media and converts to mp3? Im ooking for a free program that can record streaming, realmedia, online radio, youtube sound and music and convert them into mp3 files. The best I found so far was audacity although I came across a demo of a very goo dproduct that recorded just the song and not the background sounds on the computer. It even blacked out lags in the sound.-- Jacobin1949 17:43, 9 February 2007 (UTC)
GAH! This is terrible. Somebody on the computer let one of the Adobe or Macromedia apps use the internet, and they all disabled themselves. What do I delete so that either I can use it, reinstall, or something? For some reason Distiller and Acrobat Pro 6 have not been affected. If that cannot be helped, I need a program to convert three flash documents I created into movies. .fla into .swf Thanks for any help! Mαc Δαvιs X ( How's my driving?) ❖ 18:51, 9 February 2007 (UTC)
I want to register a domain name for my first website. What is the best registry? Ie, which website registry offers the most trustworthy service for the least money. Ideas? Many thanks.
Hello, I have connected my laptop and the home PC in a network, I have done the necessary configuration so that I can access shared drive of both the laptop and the PC. But the problem is that I can't access the shared drive of the PC from my laptop, it displays the following message " (PC name) is not accessible, you might not have permission to use this network resource. Contact the administrator of the server to find out if you have access permissions", Any idea why is this happening? Note: I am able to access the shared drive of my laptop from the PC. Many thanks.
They are on the same network, and yah I am running windows xp on both sides. I am able to view all other things on the PC from the laptop such as shared printers and faxes, but only the shared folder of the PC cannot be accessed.
I know .JAR files can be launched from the command line (java -jar file), but how do I reassociate them with Windows to open on a double click? I recently upgraded my JRE/JDK to version six, however removing version five of the JRE/JDK apparently broke the file association. Any assistance would be much appreciated. Kyra ~(talk) 22:42, 9 February 2007 (UTC)
Hi folks,
I'm having some trouble setting up a wireless network adapter in a PC running Windows XP Media Centre Edition 2002 with Service Pack 2 (*gasp for breath* longest OS name ever!).. after faffing around for a while and having no joy getting it to recognise the card at all, it occurred to me to wonder - does MCE2k2 actually even support wireless networking? I can't seem to find a straight answer on that anywhere, but unlike newer versions of XP, when I go into the network connections panel, there are no references to wireless networks at all. It DOES have the 'Wireless Network Setup Wizard' in control panel, but I don't want to create a new network, just connect to an existing one. Normally what I'd do is (assuming the card was correctly installed) go into Control Panel/Network Connections/Wireless Network Connection, and work from there.. but.. there appears to be no such thing in this version of XP. Am I just nailing jelly to a wall here?
Any advice welcome, thanks! -- Cryptess 22:55, 9 February 2007 (UTC)
In Outlook 2003, how do you re-send/receive to receive old emails that were alreaded deleted in the program.
How can I make outlook automatically send and recevice messages when I am not on outlook or when my computer is off? 68.193.147.179 00:28, 10 February 2007 (UTC)
Computing desk | ||
---|---|---|
< February 8 | << Jan | February | Mar >> | February 10 > |
Welcome to the Wikipedia Computing Reference Desk Archives |
---|
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
I am writing a system which shows users' recently played songs. When a user listens to a song, details of this song are sent to my website which then inserts the artist, song, time and username into a MySQL database table. What I want to do, however, is show the 10 most listened to songs in addition to the most recent songs. I've got the most recent songs query working absolutely fine but I am facing problems with getting a list of the most played songs.
The table schema is as follows:
listenid - int(10) unsigned auto-increment key (the key row)
artist - varchar(255) (the name of the song's artist)
song - varchar(255) (the song name)
fulltext - char(64) (the artist and song name concatenated and run through MD5 cyphering - I thought this would help for counting rows since only one column needs counting as opposed to two, which I don't think is supported)
time - int(10) unsigned (the UNIX timestamp for the time when the song was submitted to the site)
Basically, I need a list of the number of rows with matching song name and artist, ordered descending. I tried using the following to no avail:
SELECT artist, song, COUNT(fulltext) AS times FROM nowplaying GROUP BY fulltext LIMIT 10
That query returns an error message saying there is a problem with the syntax near 'fulltext)'. Any ideas? RevenDS 01:35, 9 February 2007 (UTC)
See if this works:
SELECT MIN(artist), MIN(song), COUNT(fulltext) AS times FROM nowplaying GROUP BY fulltext LIMIT 10
— EncMstr 01:43, 9 February 2007 (UTC)
SELECT artist,song,COUNT(*) times FROM nowplaying GROUP BY artist,song ORDER BY times DESC LIMIT 10
@ 2007-02-09T03:26Z
AS times
, and so GROUP BY
should refer to the same name, i.e. GROUP BY times
instead of GROUP BY fulltext
. If AS times
was omitted, you should use GROUP BY COUNT(fulltext)
. Feel free to correct if I'm wrong. —
Xhantar
Talk 07:26, 9 February 2007 (UTC)
group by count(*)
wouldn't give a useful result. Group by
doesn't have to reference a field which appears in the select_list. COUNT(*) AS times
creates a new variable, so to speak. I had trouble escaping fulltext to prevent MySQL from using it as a keyword: and that's what the errors I got seemed to be pointing to. —
EncMstr 07:40, 9 February 2007 (UTC)@ 2007-02-09T07:38Z
GROUP BY
on multiple fields. For clarity, I was refering to this; this is the SQL that will successfully retrieve a list of the most-mentioned artist/song combination:SELECT artist, song, COUNT(*) AS times FROM nowplaying GROUP BY artist, song ORDER BY times DESC LIMIT 10
I have two SATA 160 GB drives that I would like to arrange such that there are three small RAID 0 partitions for WinXP, OpenSUSE and Ubuntu and a large RAID 1 partition(s) for important data. Is it possible to have the same drives set up like this? Is it complicated? -- Seans Potato Business 06:11, 9 February 2007 (UTC)
Is there freeware for website design, best if it is WYSIWYG (probably) ... that I can use to design, test, and demonstrate my website without actually publishing it to the web on a server? My 'site', I suppose, would be a file that could be viewed by my browser (or I can send the file to someone else who could view it on their browser...), as though I were online, but without actually being online where my incomplete or confidential information might be accessed. (...hate sounding like such a rookie, but you go to war with the army you have, so to speak...)
I have an
X.509 certificate which is being used on our local mail server. I want to sign this certificate with my
OpenPGP key so that someone who trusts me trusts me will trust the server. I have an inkling that this is possible (we're using
Enigmail with
Mozilla Thunderbird), but I have no idea how to go about it. Is it worth doing? Is it possible? I fetched the cert with openssl s_client -connect myserver:993
, and I have it in
ASCII armored format. I'm using
GPG to manage my OpenPGP keys.
grendel|
khan 16:47, 9 February 2007 (UTC)
Where can I find a FREE program that tapes/records streaming media and converts to mp3? Im ooking for a free program that can record streaming, realmedia, online radio, youtube sound and music and convert them into mp3 files. The best I found so far was audacity although I came across a demo of a very goo dproduct that recorded just the song and not the background sounds on the computer. It even blacked out lags in the sound.-- Jacobin1949 17:43, 9 February 2007 (UTC)
GAH! This is terrible. Somebody on the computer let one of the Adobe or Macromedia apps use the internet, and they all disabled themselves. What do I delete so that either I can use it, reinstall, or something? For some reason Distiller and Acrobat Pro 6 have not been affected. If that cannot be helped, I need a program to convert three flash documents I created into movies. .fla into .swf Thanks for any help! Mαc Δαvιs X ( How's my driving?) ❖ 18:51, 9 February 2007 (UTC)
I want to register a domain name for my first website. What is the best registry? Ie, which website registry offers the most trustworthy service for the least money. Ideas? Many thanks.
Hello, I have connected my laptop and the home PC in a network, I have done the necessary configuration so that I can access shared drive of both the laptop and the PC. But the problem is that I can't access the shared drive of the PC from my laptop, it displays the following message " (PC name) is not accessible, you might not have permission to use this network resource. Contact the administrator of the server to find out if you have access permissions", Any idea why is this happening? Note: I am able to access the shared drive of my laptop from the PC. Many thanks.
They are on the same network, and yah I am running windows xp on both sides. I am able to view all other things on the PC from the laptop such as shared printers and faxes, but only the shared folder of the PC cannot be accessed.
I know .JAR files can be launched from the command line (java -jar file), but how do I reassociate them with Windows to open on a double click? I recently upgraded my JRE/JDK to version six, however removing version five of the JRE/JDK apparently broke the file association. Any assistance would be much appreciated. Kyra ~(talk) 22:42, 9 February 2007 (UTC)
Hi folks,
I'm having some trouble setting up a wireless network adapter in a PC running Windows XP Media Centre Edition 2002 with Service Pack 2 (*gasp for breath* longest OS name ever!).. after faffing around for a while and having no joy getting it to recognise the card at all, it occurred to me to wonder - does MCE2k2 actually even support wireless networking? I can't seem to find a straight answer on that anywhere, but unlike newer versions of XP, when I go into the network connections panel, there are no references to wireless networks at all. It DOES have the 'Wireless Network Setup Wizard' in control panel, but I don't want to create a new network, just connect to an existing one. Normally what I'd do is (assuming the card was correctly installed) go into Control Panel/Network Connections/Wireless Network Connection, and work from there.. but.. there appears to be no such thing in this version of XP. Am I just nailing jelly to a wall here?
Any advice welcome, thanks! -- Cryptess 22:55, 9 February 2007 (UTC)
In Outlook 2003, how do you re-send/receive to receive old emails that were alreaded deleted in the program.
How can I make outlook automatically send and recevice messages when I am not on outlook or when my computer is off? 68.193.147.179 00:28, 10 February 2007 (UTC)