Solved

select unique records from mysql table

Posted on 2010-11-09
14
424 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 50 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now