?
Solved

select unique records from mysql table

Posted on 2010-11-09
14
Medium Priority
?
436 Views
Last Modified: 2013-12-12
assuming that I have more than one row with the same value in the name field
select distinct name_field from members_table
will give me a list of unique values in colname from a table
ie
michael
lisa
tony

what I want to show is the entire row for each record
ie
michael moore male
lisa taylor female
tony burke male


0
Comment
Question by:joomla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +2
14 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34093270
Here is an example on my system for oracle database



create or replace package stragg as
    function result(i in integer) return varchar2;
    function collect(i in integer, s in varchar2) return integer;
end;

create or replace package body stragg as
   type TStringTable is table of long index by binary_integer;
   tab TStringTable;
   iNullIndex binary_integer := -1;
   bCanEmpty boolean := False;
   function result(i in integer) return varchar2 is
     itmp binary_integer := nvl(i,iNullIndex);
   begin
     bCanEmpty := True;
     if tab.exists(itmp) then
       return tab(itmp);
     else
       return NULL;
     end if;
   end;
   function collect(i in integer, s in varchar2) return integer is
     itmp binary_integer := nvl(i,iNullIndex);
     stmp long := substr(s,1,4000);
   begin
     if bCanEmpty then -- after result(..) we can clean up here
       tab.delete;
       bCanEmpty := False;
     end if;
     if tab.exists(itmp) then
       tab(itmp) := substr( tab(itmp)|| ',' || s, 1, 4000);
     else
       tab(itmp) := stmp;
     end if;
     return 1;
   end;
 end;

    select deptno, stragg.result(deptno) as NAMES
    from emp
    where stragg.collect( deptno, ename) = 1
    group by deptno;

OCP>/

    DEPTNO NAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES



    select d.deptno, stragg.result(d.deptno) as NAMES
    from emp e,dept d
    where stragg.collect(d.deptno, e.ename) = 1
    and d.deptno = e.deptno(+)
    group by d.deptno
OCP>/

    DEPTNO NAMES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,ADAMS,FORD,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD
        40

Open in new window

0
 
LVL 14

Expert Comment

by:Scott Madeira
ID: 34094008
How about this:

select distinct first_name, last_name, gender from members_table

This will give you all distinct combinations of your three fields.  If you have multiple tony burkes in your database you will only get back one row.  if you have a lisa taylor male and a female lisa taylor you would get two rows one for the male and one for the female.

If this isn't what you are asking for please clarify...
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 34094104
What it sounds like is you are looking for the first row with field matching your criteria... and you only want one row for each specific criteria found... I am hoping you have a unique primary key called id or something on the table so you could do

select first_name, last_name, gender from members where id = (select b.id from members b where first_name = "your criteria" LIMIT 1);
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 34094168
if you don't have an id field or other primary key where you can do that you could compare the concatenated fields

select distinct first_name, last_name, gender from members where concat(first_name,last_name,gender) = (select concat(b.first_name,b.last_name,b.gender) from members b where first_name = "your criteria" LIMIT 1);


added distinct right at the start in case you have multiple people with the same first_name, last_name, and gender...
0
 

Author Comment

by:joomla
ID: 34095648
Hi jrm213jrm213:
I have a database used to capture/store the name of subscribers to a newsletter.
I've noticed that the same email address has been submitted mulitple times, not always with the same name.
I want a script that will only distribute a newsletter once to each email address.
The newsletter will be personalised with the recipients firstname I can't simply distinct on email address because I want to include the firstname

I hope that gives you some more insight
thanks for your help
M
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 34095873
does your members table have a unique primary key like id?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34096948
Following from jrm's question, if you have a unique id on the table, and it is named "id", then you can use this

select * from members_table
where id in (
      select MAX(id)
      from members_table
      group by name_field
)
0
 

Author Comment

by:joomla
ID: 34096955
yes it does
fieldname="addr_id"
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34096990
Something like this.  Adjust the group by to list the columns you want just ONE result for.

select * from members_table
where addr_id in (
      select MAX(addr_id)
      from members_table
      group by email
)
0
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 34097064
hi cyberwiki's example should work, it would be much tougher without that uniqueid


0
 

Author Comment

by:joomla
ID: 34097426
Hi,
cyberwiki's solution appears to timeout.
there are 10,000 records and I've let it run for over 2 minutes with not result

the simple 'select * from members_table displays the 10,000 in about 6 seconds
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34097645
The plan for the IN clause may be bad, try the JOIN form:

select b.*
from
(     select MAX(addr_id) addr_id
      from members_table
      group by email
) a, members_table b
where a.addr_id = b.addr_id
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 200 total points
ID: 34097646
Come back if it doesn't finish in 2 minutes.
0
 

Author Closing Comment

by:joomla
ID: 34134127
Thanks cyberkiwi:
sorry for the delay in responding.
I'm happy to confirm your solution worked wonderfully
regards
M
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question