Simpleish sort question?

**frizzle** · Jul 17 '05, 12:27 PM

Re: Simpleish sort question?

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** · Jul 17 '05, 12:27 PM

Re: Simpleish sort question?

Mark wrote:
[color=blue]
>
> Say there is a database of jokes, from different jokers...
>[/color]
<snip>[color=blue]
>
> I am sure I once saw a way of sorting an array so that only one joke from
> each joker remained...
>[/color]

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_submit ted) 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=rec ent.joker AND jokes.date_subm itted=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** · Jul 17 '05, 12:27 PM

Re: Simpleish sort question?

Mark wrote:
[color=blue]
> Hi yall[/color]

Hi Mark,
[color=blue]
>
> I'm a newbie, forgive me...
>[/color]

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

[color=blue]
> 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)[/color]

And the userid and the submitdate.
I take it you stored that too.
[color=blue]
>
> 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...[/color]

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.

[color=blue]
>
> Or am I having some sort of acid flashback?[/color]

Nevermind them.

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

http://odegaard.no-ip.com/morten/index.php?page=fun/flash/flashback.swf&pagetype=flash&menu=

[color=blue]
>
> Can anyone help?
>
> TTTIA[/color]

You are welcome.

Regards,
Erwin Moller
[color=blue]
>
> Mark
>
> PS reading this back it looks like I'm taking the piss, but I am actually
> quite serious :0)[/color]

**frizzle** · Jul 17 '05, 12:27 PM

Re: Simpleish sort question?

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

**Mark** · Jul 17 '05, 12:27 PM

Re: Simpleish sort question?

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" <hahaha@hahaha. com> wrote in message
news:1112780541 .48bfd9a42ea6f8 5cf2187dbe854d5 a94@teranews...[color=blue]
> 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)
>
>
>[/color]

**llbbl** · Jul 17 '05, 12:28 PM

Re: Simpleish sort question?

Hey uhhh . Did anyone read this article in here?

fw_error_www

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

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

**Mark** · Jul 17 '05, 12:28 PM

Re: Simpleish sort question?

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

"llbbl" <llbbl@yahoo.co m> wrote in message
news:1112824989 .097641.137140@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> 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. :)
>[/color]

Simpleish sort question?

Simpleish sort question?

Comment

Comment

Comment

Comment

Comment

Comment

Comment