Welcome Guest, Not a member yet? Register   Sign In
Select records where certain value is unique
#1

[eluser]mvdg27[/eluser]
Hi guys,

I'm trying to figure out how to create a query that can select rows from the database, where one specific field has to be unique. Others can be similar, that's why Distinct won't work ..

Let me show you some code:

Code:
$this->db->distinct();
$this->db->select('songs.id, songs.name AS song, artists.name AS artist, songs.url AS url');    
$this->db->order_by('datetime', 'desc');
$this->db->join('songartistDefs', 'songartistDefs.song_id = songs.id');
$this->db->join('artists', 'artists.id = songartistDefs.artist_id');
$query = $this->db->get('songs', 3);
$latest_songs = $query->result('array');

The context is that one song can belong to multiple artists, but in the list of most recent songs, I don't want any duplicate songs. So songs.id should be a unique value.

How can I achieve this in my query?

Any help would be greatly appreciated!

Cheers, Michiel
#2

[eluser]frenzal[/eluser]
$this->db->groupby('songs.id'); ought to do the trick
#3

[eluser]mvdg27[/eluser]
Yes indeed, that did the trick! Thanks for the quick reply!




Theme © iAndrew 2016 - Forum software by © MyBB