In a SQL query that returns multiple fields, how to eliminate duplicate values of one field only

I have the following 3 database tables:

CREATE TABLE public.courserecord
(
  courserecordid numeric(38) NOT NULL,
  studentid numeric(38) NOT NULL,
  courseinstanceid numeric(38) NOT NULL,
..........................
)

CREATE TABLE public.useraction
(
  useractionid numeric(10) NOT NULL,
  userid numeric(10) NOT NULL,
  useractiontypeid numeric(10),
  actiondate timestamp,
  CONSTRAINT useraction_pkey PRIMARY KEY (useractionid),
  CONSTRAINT usact_uact_fk FOREIGN KEY (useractiontypeid) REFERENCES public.useractiontype (useractiontypeid) ON UPDATE NO ACTION ON DELETE NO ACTION
) WITH OIDS;

and

CREATE TABLE public.useractiondata
(
  useractiondataid numeric(10) NOT NULL,
  accountid numeric(10),
  courserecordid numeric(10),
............
CONSTRAINT crsrec_usacd_fk FOREIGN KEY (courserecordid) REFERENCES public.courserecord (courserecordid) ON UPDATE NO ACTION ON DELETE NO ACTION,
..................
)

If i run the following query:

SELECT useractiondata.courserecordid, useraction.actiondate
FROM   useraction, useractiondata
WHERE useraction.useractionid = useractiondata.useractionid
AND   useraction.useractiontypeid = 30
ORDER BY useractiondata.courserecordid;

the results look something like this:

courserecordid | actiondate
----------------------------------
150                       2007-02-21
151                       2007-02-21
1258                     2006-12-19
2330                     2006-08-14
3539                     2006-07-25
..............................................
8122                      2006-10-31
8122                      2006-11-01
...............................................
9031                      2006-10-03
9252                      2006-09-14
...............................................
9850                      2006-09-19
9850                      2006-09-19
9850                      2006-12-12
9850                      2006-12-31
.................................................

So basically most of the time for each value of 'courserecordid' there is only value of 'actiondate', but occasionally for the same value of 'courserecordid' there are multiple values of 'actiondate'. Now, what I want is a query that will return just one value of 'actiondate' for each value of 'courserecordid'. If there are multiple values of 'actiondate' for certain value of 'courserecordid' it should only return the earliest one. So, the results should look like this:

courserecordid | actiondate
----------------------------------
150                       2007-02-21
151                       2007-02-21
1258                     2006-12-19
2330                     2006-08-14
3539                     2006-07-25
..............................................
8122                      2006-10-31
...............................................
9031                      2006-10-03
9252                      2006-09-14
...............................................
9850                      2006-09-19
.................................................

Basically, the same as above but if there are several rows with the same 'courserecordid' only return the one with the earliest value for 'actiondate'.

How do I do that?
Lou1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ee_rleeCommented:
try this
SELECT useractiondata.courserecordid, MIN(useraction.actiondate)
FROM   useraction, useractiondata
WHERE useraction.useractionid = useractiondata.useractionid
AND   useraction.useractiontypeid = 30
GROUP BY useractiondata.courserecordid
ORDER BY useractiondata.courserecordid;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
syedasimmeesaqCommented:
Try distinct keyword

SELECT distinct useractiondata.courserecordid, MIN(useraction.actiondate)
FROM   useraction, useractiondata
WHERE useraction.useractionid = useractiondata.useractionid
AND   useraction.useractiontypeid = 30
GROUP BY useractiondata.courserecordid
ORDER BY useractiondata.courserecordid;
0
ee_rleeCommented:
@syedasimmeesaq hi. i think that it is not necessary to add distinct in the query. by using group by, the courserecordid would already be distinct, wouldn't it?
0
Lou1Author Commented:
Thanks. That worked great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.