• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.