How to retrieve two rows of same field in one table
Moderator: General Moderators
-
albertlive
- Forum Newbie
- Posts: 4
- Joined: Mon Mar 28, 2011 11:25 am
How to retrieve two rows of same field in one table
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
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
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: How to retrieve two rows of same field in one table
Code: Select all
<?php
$qry = "SELECT firstname, lastname FROM your_tbl LIMIT 2";
?>“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: How to retrieve two rows of same field in one table
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";
?>-
albertlive
- Forum Newbie
- Posts: 4
- Joined: Mon Mar 28, 2011 11:25 am
Re: How to retrieve two rows of same field in one table
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
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
-
rashidfarooq
- Forum Newbie
- Posts: 6
- Joined: Wed Mar 30, 2011 7:39 am
Re: How to retrieve two rows of same field in one table
Use this code
Code: Select all
$qry = "Select firstname, lastname from your_tbl_name where lastname = "Bond"";
-
albertlive
- Forum Newbie
- Posts: 4
- Joined: Mon Mar 28, 2011 11:25 am
Re: How to retrieve two rows of same field in one table
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
Albert
-
rashidfarooq
- Forum Newbie
- Posts: 6
- Joined: Wed Mar 30, 2011 7:39 am
Re: How to retrieve two rows of same field in one table
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 ))
-
albertlive
- Forum Newbie
- Posts: 4
- Joined: Mon Mar 28, 2011 11:25 am
Re: How to retrieve two rows of same field in one table
Thanks rashidfarooq but still it didn't work.
Albert
Albert
Re: How to retrieve two rows of same field in one table
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.)
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...
Code: Select all
SELECT SUBSTRING_INDEX(`full_name`, ' ', 1) AS `first_name` FROM `person_table` WHERE `full_name` LIKE '% Bond'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...