CODEWITHSUNDEEP
mysqlsql
Rasmus
Rasmus

Reputation: 177

sql get distinct per week

I am a novice sql programmer so i need a little help trying to get a query extraction.

I need to get the unique songs played out of a table per week.

Say in a certain week there have been played 8 different songs but those 8 songs have been played numerous times each, i just want the 8 songs to show up.

I need this done by joining 2 tabels, a STATS table and a PLAYLIST table

SELECT playlist.p_title AS title, stats.s_week AS week, playlist.p_artist AS artist, p_id
FROM stats, playlist
WHERE stats.s_pid = playlist.p_id
AND s_uid =31
ORDER BY s_week DESC
LIMIT 0 , 30

This is my original query that gives me all the songs that has been played in all weeks, even if they have been played a number of times in each week, which is what i dont want.

Anyone able to help me distinct the songs per week?

Thanks in advance - Rasmus

Upvotes: 0

Views: 631

Answers (3)

MatBailie
MatBailie

Reputation: 86706

As you want to do this for each week you're best advised to look at GROUP BY...

Also, I would advise looking for tutorials on using JOIN syntax (This has been around since 1992 and commonly considered the standard to use. Using , is also often deprecated in various RDBMS.)

SELECT
  stats.s_week        AS week,
  stats.p_id          AS pid,
  playlist.p_title    AS title,
  playlist.p_artist   AS artist,
  COUNT(*)            AS plays     /* This shows how many times a track was played */
FROM
  stats
INNER JOIN
  playlist
    ON stats.s_pid = playlist.p_id
WHERE
  s_uid =31
GROUP BY
  stats.s_week,
  stats.p_id,
  playlist.p_title,
  playlist.p_artist
ORDER BY
  stats.s_week DESC
LIMIT
  0 , 30

Where you have not include the table names on certain fields, I've also added them back in.

It's always better to be explicit about where the field comes from. And for GROUP BY, and other uses, it's often required.

Upvotes: 1

fthiella
fthiella

Reputation: 49049

Just use DISTINCT clause:

SELECT DISTINCT
  playlist.p_title AS title,
  stats.s_week AS week,
  playlist.p_artist AS artist, p_id
FROM stats inner join playlist on stats.s_pid = playlist.p_pid
WHERE s_uid =31
ORDER BY s_week DESC
LIMIT 0 , 30

also, it is better to use a table1 inner join table2 on table1.id = table2.id instead of using the WHERE clause to join the two tables together.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13496

SELECT playlist.p_title AS title, stats.s_week AS week, playlist.p_artist AS artist, p_id
FROM stats, playlist
WHERE stats.s_pid = playlist.p_id
AND s_uid =31
group by playlist.p_title, stats.s_week,playlist.p_artist
ORDER BY s_week DESC
LIMIT 0 , 30

Upvotes: 0

Related Questions