• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1573
  • Last Modified:

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?
0
Lou1
Asked:
Lou1
  • 2
1 Solution
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now