Simpleish sort question?

[Mark]

Hi yall

I'm a newbie, forgive me...

I am sure I have seen a simple way to do this, but I can't for the life of
me remember how it was done...

Say there is a database of jokes, from different jokers...

Some jokers submit lots of jokes, others only one...

I want to display the newest joke submitted by each joker, in a list sorted
by newness
(using, say, the joke_id primary key)

I only want one joke to show from each joker

And I want every joker to be represented in the list

I am sure I once saw a way of sorting an array so that only one joke from
each joker remained...

Or am I having some sort of acid flashback?

Can anyone help?

TTTIA

Mark

PS reading this back it looks like I'm taking the piss, but I am actually
quite serious :0)

[frizzle]

SELECT DISTINCT joker, joke FROM jokes ORDER BY joke_id

Not sure if this works correctly, but i believe DISTINCT is your key to
succes

[Colin McKinnon]

Mark wrote:

Say there is a database of jokes, from different jokers...
<snip>
I am sure I once saw a way of sorting an array so that only one joke from
each joker remained...

Kind of tricky to do with a PHP array. Easier to do with SQL (so not a PHP
question)...you could try

SELECT joker, MAX(date_submitted) as ident FROM jokes GROUP BY joker

Then to get the corresponding jokes, using the parameters retruned by the
above:

SELECT joke FROM jokes WHERE joker='$joker' AND date_submited='$ident'

This requires lots of queries though. It can be done with one. The
'simplest' way is to create a view based on the first query above...say
recent_jokes then:

SELECT recent.joker, ident, joke FROM recent,jokes
WHERE jokes.joker=recent.joker AND jokes.date_submitted=ident

But not all DBMS support views so....

SELECT joker, MAX(CONCAT(date_submitted,joke)) AS jokedata FROM jokes GROUP
BY joker

....which should also be the most efficient query. Obviously you'll need to
split jokedata in your code.

HTH

C.

[Erwin Moller]

Mark wrote:

Hi yall
Hi Mark,

I'm a newbie, forgive me...

In the absence of the Pope, I'll do the forgiving.
You are forgiven.
:-)

I am sure I have seen a simple way to do this, but I can't for the life of
me remember how it was done...

Say there is a database of jokes, from different jokers...

Some jokers submit lots of jokes, others only one...

I want to display the newest joke submitted by each joker, in a list
sorted by newness
(using, say, the joke_id primary key)
And the userid and the submitdate.
I take it you stored that too.

I only want one joke to show from each joker

And I want every joker to be represented in the list

I am sure I once saw a way of sorting an array so that only one joke from
each joker remained...
Well, many ways.

An easy way (but maybe not the fastest)
[postgresql style]

CREATE TABLE tbljokes(
jokeid SERIAL PRIMARY KEY,
submitdate date,
userid integer REFERENCES tbluser(userid),
thejoke text
)

Suppose you have the above table.

For sake of simplicity, let us assume that a higher jokeid means a later
submitdate.
Is that reasonable to assume?

Now you use something like:
SELECT userid, MAX(jokeid) AS latestjokeid FROM tbljokes
GROUP BY userid;

to get the latest jokeid per user.
OK?

Now we can use that trick to rewrite the query:
SELECT jokeid, submitdate, userid, thejoke
FROM tbljokes
WHERE ( jokeid IN
(SELECT jokeid FROM
(
SELECT userid, MAX(jokeid) AS latestjokeid FROM tbljokes
GROUP BY userid;
)
)
) ORDER BY submitdate;

Something like that.
I didn't test.


Or am I having some sort of acid flashback?
Nevermind them.

Go here and view the flash animation to get better. :P

http://odegaard.no-ip.com/morten/ind...pe=flash&menu=

Can anyone help?

TTTIA
You are welcome.

Regards,
Erwin Moller

Mark

PS reading this back it looks like I'm taking the piss, but I am actually
quite serious :0)

[frizzle]

*Assuming you're using mySQL, for more info look in the mySQL function
list under DISTINCT.

[Mark]

Thanks to everyone for their responses...

I love you all.

I feel it's an appropriate time for a joke...

Q. What's the most difficult thing about rollerblading?

A. Telling your parents that you're gay.
(Obviously no offence is meant; either to rollerbladers, or homosexuals...
;0)
"Mark" <ha****@hahaha.com> wrote in message
news:1112780541.48bfd9a42ea6f85cf2187dbe854d5a94@t eranews...

Hi yall

I'm a newbie, forgive me...

I am sure I have seen a simple way to do this, but I can't for the life of
me remember how it was done...

Say there is a database of jokes, from different jokers...

Some jokers submit lots of jokes, others only one...

I want to display the newest joke submitted by each joker, in a list
sorted by newness
(using, say, the joke_id primary key)

I only want one joke to show from each joker

And I want every joker to be represented in the list

I am sure I once saw a way of sorting an array so that only one joke from
each joker remained...

Or am I having some sort of acid flashback?

Can anyone help?

TTTIA

Mark

PS reading this back it looks like I'm taking the piss, but I am actually
quite serious :0)

[llbbl]

Hey uhhh . Did anyone read this article in here?

http://dev.mysql.com/tech-resources/...s/ddws/10.html

I think Mark did and is why he is asking this question. :)

[Mark]

My joke was much funnier than theirs (even without seeing the punchlines...
:0)

"llbbl" <ll***@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hey uhhh . Did anyone read this article in here?

http://dev.mysql.com/tech-resources/...s/ddws/10.html

I think Mark did and is why he is asking this question. :)