Page 1 of 1

How to retrieve two rows of same field in one table

Posted: Mon Mar 28, 2011 12:11 pm
by albertlive
Hi Guys,

How to retrieve two rows of same field in one table on a single query in phpmyadmin or in php script.

For example: I want to get the top 2 firstname of the same lastname of the record, row1_firstname and row2_firstname.

James Bond
Mark Bond
Jason Bond

The result will be:
James Bond
Mark Bond

Thanks,
Albert

Re: How to retrieve two rows of same field in one table

Posted: Mon Mar 28, 2011 1:16 pm
by social_experiment

Code: Select all

<?php
 $qry = "SELECT firstname, lastname FROM your_tbl LIMIT 2";
?>
That will select the two latest entries in the database. If you want the first two on the database you can order them in an ascending fashion.

Re: How to retrieve two rows of same field in one table

Posted: Mon Mar 28, 2011 2:09 pm
by fugix
to get the first two rows, im going to add a little to social experiments code...set the beginning limit to 0 or 1,

Code: Select all

<?php
 $qry = "SELECT firstname, lastname FROM your_tbl LIMIT 0, 2";
?>

Re: How to retrieve two rows of same field in one table

Posted: Tue Mar 29, 2011 10:31 pm
by albertlive
Thanks for the reply Guys, but still it didn't work.

I have lots of record and i want to get all firstname from each same lastname.

Example:
James Bond
Jeff Craig
Carter Love
Mark Bond
Josh Craig
Bob Love
Jason Bond
Kris Craig
Albert Love

The result will be:
James Bond
Mark Bond
Jeff Craig
Josh Craig
Carter Love
Bob Love

Thanks in advance,
Albert

Re: How to retrieve two rows of same field in one table

Posted: Wed Mar 30, 2011 7:47 am
by rashidfarooq
Use this code

Code: Select all

$qry = "Select firstname, lastname from your_tbl_name where lastname = "Bond"";


Re: How to retrieve two rows of same field in one table

Posted: Wed Mar 30, 2011 11:03 pm
by albertlive
Thanks rashidfarooq but the problem is i have lots of record and i can't filter all the surename to be use in the query.


Albert

Re: How to retrieve two rows of same field in one table

Posted: Thu Mar 31, 2011 12:56 pm
by rashidfarooq
I have Understood your problem now. I Hope this code will work fin to solve your problem.

Code: Select all

Select firstname from tblname where lastname in (SELECT lastname FROM tblname GROUP BY lastname HAVING (COUNT(lastname) > 1 ))


Re: How to retrieve two rows of same field in one table

Posted: Tue Apr 05, 2011 9:48 pm
by albertlive
Thanks rashidfarooq but still it didn't work.

Albert

Re: How to retrieve two rows of same field in one table

Posted: Wed Apr 06, 2011 10:16 am
by McInfo
If the full name is in a single field, you can use a query like this to get the first name. (It assumes that a person's first name does not contain a space.)

Code: Select all

SELECT SUBSTRING_INDEX(`full_name`, ' ', 1) AS `first_name` FROM `person_table` WHERE `full_name` LIKE '% Bond'
The SUBSTRING_INDEX() function returns a substring from the beginning of the first argument (the full_name field) up to the Nth occurrence of the second argument (a space) where N is the third argument (1).

The LIKE keyword matches the column before it against the pattern after it. In the pattern, the percent sign (%) is a wildcard character that matches a string of zero or more characters, so the pattern "% Bond" matches a full_name that ends with a space and the last name Bond.

If you frequently need to search for people by their first or last name, alter the table structure by splitting the full_name field into two fields: first_name and last_name. Then searching will be more efficient because MySQL won't have to do string operations on the full_name.

This article could be useful: ...xaprb.com/...firstleastmax-row-per-group...