Solved

select unique records from mysql table

Posted on 2010-11-09
14
425 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 25
Split long text string into shorter chunks 7 23
Process mapping 5 31
str_replace not working in php script 4 14
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

863 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

27 Experts available now in Live!

Get 1:1 Help Now