[SOLVED] select three unique records from database....pls help

[paparanch]

good day everyone!

 

here's my table:

 

id  |  name  |  category  |

  1  | randy  |  student  |

  2  | james  |  student  |

  3  | Karl    |  student  |

  4  | John    |  worker    |

  5  | Joew  |    worker    |

  6  | David  |  worker    |

  7  | Mike    |    teacher  |

 

Now, what i wanted to do is SELECT 3 unique records with different category(desc).

 

SAMPLE OUTPUT:

 

  Mike - teacher

  David- worker

  Karl - student

 

here is my code so far:

 

<? 
$query  = "SELECT * FROM members ORDER BY member_id desc LIMIT 3";
$result = mysql_query($query);

while($info = mysql_fetch_array($result)){

$membername = $info['member_name']; 
$member_cat = $info['member_cat']; 

echo "$member_name-$member_cat<br>";

}
?>

 

THE OUTPUT(which is wrong)

 

 

    7  | Mike    |    teacher  |

    6  | David  |  worker    |

    5  | Joew  |    worker    |

 

 

 

 

 

[Mchl]

SELECT * FROM members GROUP BY category LIMIT 3

[paparanch]

tnx for that guru!

but its not working for me i don't why...

 

because when i add new record which is:

 

8 - Don - Principal

 

it didn't display, which is supposed the first to display as the latest record...??

[phpdragon]

SELECT * FROM members GROUP BY category LIMIT 3 DESC

[phpdragon]

do you want to select users based on type and then ordered from last entered?

[paparanch]

i already tried that one mr.phprdagon but stillnot working...

let me explain again..

 

i have record, let say:

 

1 - David - teacher

2 - MIke - student

3 - John - worker

4 - James - student

5 - Jake - student

6 - Don - worker

 

it this table..Don is the latest added record..so the output should:

 

6 - Don - worker

5 - Jake - student

1 - David - teacher

 

notice that they have different category...

 

and here is the output of my code based on that table(which is wrong):

 

6 - Don - worker

5 - Jake - student

4 - James - student

 

notice that it display two record with the same category, it should not display record with the same category...how am i going to do this?

 

 

 

[phpdragon]

so you want to get the last user added from each type, so the last worker, the last teacher and the last student, you can do 3 queries, someone may be able to shorten it for you

 

SELECT * FROM members WHERE category='teacher' LIMIT 1 DESC

SELECT * FROM members WHERE category='student' LIMIT 1 DESC

SELECT * FROM members WHERE category='worker' LIMIT 1 DESC

[paparanch]

ahmnnn....its not really selecting the last added record on every category.

what i wanted is to get the 3 latest added record

but if in case the second record is the same category with the first or third,  it will go to the next latest record and see if its different category...etc...until it will display 3 records with different category...

[phpdragon]

can there be more than these 3 categories if not then the 3 queries will give you the result you want but change slightly to this

 

SELECT * FROM members WHERE category='teacher' ORDER BY id DESC LIMIT 1

SELECT * FROM members WHERE category='student' ORDER BY id DESC LIMIT 1

SELECT * FROM members WHERE category='worker' ORDER BY id DESC LIMIT 1

 

the reason that would work is because it orders it by id, the highest number id will always be the last entry, the only reason these queries wont do what you want would be if there are more than 3 category types is that the case?

[kickstart]

Hi

 

Think this will do it:-

 

SELECT * FROM members where id IN (SELECT MAX(id) FROM members GROUP BY category) ORDER BY id LIMIT 3

 

All the best

 

Keith

[paparanch]

yah mr.phpdragon!

i have more than three categories...

 

thnx mr.kickstart i will try that query...

[paparanch]

Hi

 

Think this will do it:-

 

SELECT * FROM members where id IN (SELECT MAX(id) FROM members GROUP BY category) ORDER BY id LIMIT 3

 

All the best

 

Keith

 

tnx for this mister but its not working for me....yah, it display three record with different category but it did not display the latest ones...

[paparanch]

wow! it works mr.kickstart! i just added "desc" after the "ORDER BY"...thx a lot!

problem solved!