Quick question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Quick question

Post by evilmonkey »

Sorry about the uninformative title. :oops: I have a table in which I have a few rows. I want to pull the first four rows, but I want to make sure that one of the feilds is different in each of them. So something like this:

id | name | birthday
1 evilmonkey 12/02/87
2 emjokes 15/05/75
3 user124 18/12/83
4 pseudo12 15/05/75
5 apple_pie 29/01/90

See, emjokes and pseudo12 have the same birthday. I want to select only emjokes in that case. (BTW, this isn't for birthdays, it's for something else. :) ) It's only for one feild (in this case, I only don't want matching birthdays.) How can I set up a query for that?

Thanks. :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you use DISTINCT('birthday') with a GROUP BY birthday LIMIT 0,4?
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Everah wrote:Can you use DISTINCT('birthday') with a GROUP BY birthday LIMIT 0,4?
Perhaps...how would I use that with a select statement? :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

<?php
$sql = "SELECT id, name, DISTINCT('birthday') AS bday 
        FROM my_table 
        GROUP BY birthday 
        LIMIT 0, 4";
?>
Come to think of it, you might be able to do away with the DISTINCT altogether and just use the group by clause, but try it with DISTINCT first.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Afaik, the DISTINCT operator applies to *all* columns in a SELECT clause, not only the first right from it...

thus, if you have a column with rows: (a1, b) and (a1, c) and you SELECT DISTINCT (col1), col2 you still get (a1, b) and (a1, c).
thus, a simple SELECT DISTINCT without groupby will do...
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Thank you very much Everah and Tim, Tim you're right, it worked. :)
Post Reply