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