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) 7 1350
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
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_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.
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)
*Assuming you're using mySQL, for more info look in the mySQL function
list under DISTINCT.
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 .48bfd9a42ea6f8 5cf2187dbe854d5 a94@teranews... 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)
My joke was much funnier than theirs (even without seeing the punchlines...
:0)
"llbbl" <ll***@yahoo.co m> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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. :) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave [Hawk-Systems] |
last post by:
have the table "numbercheck"
Attribute | Type | Modifier
-----------+------------+----------
svcnumber | integer | not null
svcqual | varchar(9) |
svcequip | char(1) |
svctroub | varchar(6) |
svcrate | varchar(4) |
svcclass | char(1) |
trailer | varchar(3) |
|
by: Együd Csaba |
last post by:
Hi All,
how can I improve the query performance in the following situation:
I have a big (4.5+ million rows) table. One query takes approx. 9 sec to
finish resulting ~10000 rows. But if I run simultaneously 4 similar queries
it takes nearly 5 minutes instead of 4 times 9 seconds or something near of
that.
here is a sample query:
select ...
|
by: Chuck M |
last post by:
Is there a simple way to change the sort order in Access (numeric-alpha)
to alphanumeric? I've searched the help files and some books, but found
no answer.
Thanx for any advice
Chuck
|
by: Joe Fallon |
last post by:
I have a WinForm that is a Base class. It has many controls on it and a lot
of generic code behind.
When I inherit the form I override 5 methods and now each child form has the
same look and feel and functionality. This part all works fine.
(I learned this morning that if you override a method that also has an Event
Handler then you should...
|
by: yeti349 |
last post by:
Hi, I'm using the following code to retrieve data from an xml file and
populate a javascript array. The data is then displayed in html table
form. I would like to then be able to sort by each column. Once the
array elements are split, what is the best way to sort them? Thank you.
//populate data object with data from xml file.
//Data is a...
| |
by: Jan Smith |
last post by:
I've searched the overloads for the Array.Sort method, and I haven't found a
clear answer to my question. Maybe it's not in Array.Sort.
Here's the question:
I initialize an array X with the values 28 142 3 17 225.
I can sort this array in ascending order and it will return 3 17 28 142 225.
But I want a method that will return the...
|
by: Jack |
last post by:
Hi,
I was asked this question during an interview. It seems very simple
but I don't know if there is a more efficient way.
The question said, we have 2 arrays as follows
A =
B =
As you can see both arrays are sorted. Also we see that B has enough
space for all the elements in A. We want to put element A in to B
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?...
|
by: Chad |
last post by:
I was looking at some old posts in comp.lang.c and found the following
http://groups.google.com/group/comp.lang.c/browse_thread/thread/d26abbdf4d99abd9/b3b5046326994e18?hl=en&lnk=gst&q=recurrence+relation#b3b5046326994e18
I have some questions regarding the post.
First, and I quote
"Given an algorithm with loops or recursive calls, the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |