Home » SQL & PL/SQL » SQL & PL/SQL » how to write this sql query
how to write this sql query [message #324962] Wed, 04 June 2008 11:36 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

How can I query a unique list of users?

create table report(
user_id number,
first varchar2(20),
last varchar2(20),
report_date date,
actual_date date,
constraint report_pk primary key (user_id,report_date,actual_date);


insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'John','Doe','04/01/2008','06/04/2008 10:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','06/01/2008','06/05/2008 11:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Jane','Doe','04/02/2008','06/07/2008 08:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Lisa','Simpson','03/01/2008','06/14/2008 19:36');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Bart','Simpson','03/01/2008','06/17/2008 19:37');
insert into report(user_id,first,last,report_date,actual_date)
values(1,'Bart','Simpson','05/01/2008','06/11/2008 15:37');

I would like to get a unique list of of all the users.

Thanks,

Tom


Re: how to write this sql query [message #324963 is a reply to message #324962] Wed, 04 June 2008 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

SELECT DISTINCT
Re: how to write this sql query [message #324988 is a reply to message #324962] Wed, 04 June 2008 12:51 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
bztom33 wrote on Wed, 04 June 2008 12:36
Hi,

How can I query a unique list of users?

create table report(
user_id number,
first varchar2(20),
last varchar2(20),
report_date date,
actual_date date,
constraint report_pk primary key (user_id,report_date,actual_date);


orcl10g SCOTT>l
  1  create table report(
  2  user_id number,
  3  first varchar2(20),
  4  last varchar2(20),
  5  report_date date,
  6  actual_date date,
  7* constraint report_pk primary key  (user_id,report_date,actual_date)
orcl10g SCOTT>/
constraint report_pk primary key (user_id,report_date,actual_date)
                                                                 *
ERROR at line 7:
ORA-00907: missing right parenthesis


So, I took the effort to fix that problem, then
orcl10g SCOTT>l
  1  insert into report(user_id,first,last,report_date,actual_date)
  2* values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36')
orcl10g SCOTT>/
values(1,'Jane','Doe','01/01/2008','06/04/2008 09:36')
                      *
ERROR at line 2:
ORA-01843: not a valid month

[Updated on: Wed, 04 June 2008 12:52]

Report message to a moderator

Re: how to write this sql query [message #325050 is a reply to message #324962] Wed, 04 June 2008 22:35 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>values(1,'Bart','Simpson','05/01/2008','06/11/2008 15:37');
Please remember that with Oracle when characters exist between single quote marks they are STRINGS; not date datatype.

'This is a string; 2008-06-06, not a date'
>'Bart','Simpson'
both above look like strings to me.

When you want/need a DATE datatype, then always use TO_DATE() function.
Re: how to write this sql query [message #325053 is a reply to message #324962] Wed, 04 June 2008 22:51 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
is the user_id same for all users ?
Re: how to write this sql query [message #325073 is a reply to message #324962] Wed, 04 June 2008 23:35 Go to previous messageGo to next message
itech
Messages: 168
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

u may apply the Distinct Clause
alternate, the required results can be get via group by clause as well.
Re: how to write this sql query [message #325074 is a reply to message #325053] Wed, 04 June 2008 23:35 Go to previous messageGo to next message
itech
Messages: 168
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

mintomohan wrote on Thu, 05 June 2008 08:51
is the user_id same for all users ?


he's using a composite primary key...

constraint report_pk primary key (user_id,report_date,actual_date);
Re: how to write this sql query [message #325075 is a reply to message #325073] Wed, 04 June 2008 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
itech wrote on Wed, 04 June 2008 21:35
u may apply the Distinct Clause


Previously stated in message #324963

[Updated on: Wed, 04 June 2008 23:39] by Moderator

Report message to a moderator

Re: how to write this sql query [message #325076 is a reply to message #324962] Wed, 04 June 2008 23:47 Go to previous messageGo to next message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
bztom33 wrote on Wed, 04 June 2008 22:06
Hi,

How can I query a unique list of users?




Please define user.
If user_id is same for all users, which all column should we select to identify a user ?

Regards
Minto

[Updated on: Wed, 04 June 2008 23:47]

Report message to a moderator

Re: how to write this sql query [message #325088 is a reply to message #325076] Thu, 05 June 2008 00:11 Go to previous messageGo to next message
itech
Messages: 168
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

mintomohan wrote on Thu, 05 June 2008 09:47
bztom33 wrote on Wed, 04 June 2008 22:06
Hi,

How can I query a unique list of users?




Please define user.
If user_id is same for all users, which all column should we select to identify a user ?

Regards
Minto


if we can concatinate the first and last name, then group by with the full name, then i think it will work.
Re: how to write this sql query [message #325110 is a reply to message #325088] Thu, 05 June 2008 01:03 Go to previous message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
itech wrote on Thu, 05 June 2008 10:41

if we can concatinate the first and last name, then group by with the full name, then i think it will work.



What if there are two users with the same first name and last name ?

Regards
Minto
Previous Topic: Script for Table names and row counts
Next Topic: Timestamp column
Goto Forum:
  


Current Time: Tue Dec 06 00:27:01 CST 2016

Total time taken to generate the page: 0.13530 seconds