Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

select unique records from mysql table

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
joomla
Asked:
joomla
  • 4
  • 4
  • 4
  • +2
1 Solution
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
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
 
Scott MadeiraCommented:
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
 
jrm213jrm213Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jrm213jrm213Commented:
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
 
joomlaAuthor Commented:
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
 
jrm213jrm213Commented:
does your members table have a unique primary key like id?
0
 
cyberkiwiCommented:
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
 
joomlaAuthor Commented:
yes it does
fieldname="addr_id"
0
 
cyberkiwiCommented:
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
 
jrm213jrm213Commented:
hi cyberwiki's example should work, it would be much tougher without that uniqueid


0
 
joomlaAuthor Commented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
Come back if it doesn't finish in 2 minutes.
0
 
joomlaAuthor Commented:
Thanks cyberkiwi:
sorry for the delay in responding.
I'm happy to confirm your solution worked wonderfully
regards
M
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 4
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now