Home » RDBMS Server » Server Administration » SQL
| SQL [message #373855] |
Wed, 16 May 2001 01:52  |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Dear Sir/Madam,
I felt this site is usefull for me.I have doubts regarding my project.I am expecting your reply for the below question as early as possible.
My Question:
My project is especially for Diabetes patients,where patients store their history regarding medical reports.
These Medical reports are viewed by Doctors where they have to view only the last 3 records keeping other records hidden(not to be deleted)in Database.
sir, we need a correct SQL statement to retrieve last 3 records keeping other records hidden(not to be deleted)in Database.
Thanks and regards
YAMINI & ANITHA
|
|
|
|
| Re: SQL [message #373856 is a reply to message #373855] |
Wed, 16 May 2001 02:05  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I think I can suggest a solution.
I suppose that your table will be having some field ,like date field,where we can find the order of the record. Means, the addition date of the records etc. If I take some addition_date as the key field to find the order of the records (in the descending order), I can frame a query to get the
Required number of records.
I am giving an example. The following query will fetch the records with 2nd highest salary or
3rd highest salaries depending on the value we give for “n”. If I extrapolate the same query
and hardcode value 3 in your query, I think we can solve your problem to fetch only
the recent 3 records by not fetching the other records in the database.
select empno, ename, sal
from emp a
where &n > (select count(*)
from emp b
where b.sal > a.sal) order by 3 desc
Dear all,
Pls correct if I am wrong
Regards,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373868 is a reply to message #373855] |
Wed, 16 May 2001 05:33 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373869 is a reply to message #373855] |
Wed, 16 May 2001 05:37 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373870 is a reply to message #373855] |
Wed, 16 May 2001 05:39 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373871 is a reply to message #373855] |
Wed, 16 May 2001 05:41 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373872 is a reply to message #373855] |
Wed, 16 May 2001 05:43 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373873 is a reply to message #373855] |
Wed, 16 May 2001 05:46 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373874 is a reply to message #373855] |
Wed, 16 May 2001 05:49 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373875 is a reply to message #373855] |
Wed, 16 May 2001 05:54 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373876 is a reply to message #373855] |
Wed, 16 May 2001 05:59 |
Yamini
Messages: 11
Registered: May 2001
|
Junior Member |
|
|
Hi Giridhar,
Thanks for ur reply. But as u said we don't deal with Numbers we care only last three dates. The last three dates and relevant records of the date should only be viewed rest in database. If u can help us for date it will be usefull for us. According to the example we already know to do for rest field. Once again thanks for your immediate reply.
For your convenience we have attached the structure of the table to help you. i.e., the data's should be fetched from 14/05/2001 to 19/05/2001( as per the attachment ).
Date column1 column2 column3
02/03/1999 a b v
03/08/2000 w r e
18/07/2000 wewe ghh fdgfg
14/05/2001 fdgss ff yyy
17/05/2001 aaa rr hh
19/05/2001 rtr ytty ytyr
Thanks & regards
Yamini & Anitha
|
|
|
|
| Re: SQL [message #373878 is a reply to message #373871] |
Wed, 16 May 2001 06:46 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC Which is order by 1 desc;
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373879 is a reply to message #373871] |
Wed, 16 May 2001 06:48 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC Which is order by 1 desc;
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373880 is a reply to message #373871] |
Wed, 16 May 2001 06:52 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC Which is order by 1 desc;
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373881 is a reply to message #373871] |
Wed, 16 May 2001 06:54 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC Which is order by 1 desc;
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373882 is a reply to message #373870] |
Wed, 16 May 2001 07:00 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373883 is a reply to message #373870] |
Wed, 16 May 2001 07:03 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hi Yamini,
I am making it clear to you now.
As per your mail, I created a table TEST with the following columns.
SQL> desc test
Name Null? Type
------------------------------- -------- ----
MYDATE DATE
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)
NAME3 VARCHAR2(20)
Then I inserted the records in the table and the select statement shows those records.
SQL> select to_char(mydate,'DD-MON-YYYY'),NAME1,NAME2,NAME3 FROM TEST;
TO_CHAR(MYD NAME1 NAME2 NAME3
----------- -------------------- -------------------- -------------------- --------------------
02-MAR-1999 A B C
03-AUG-2000 W R E
18-JUL-2000 WEWE GHH FDFD
17-MAY-2000 GIRI DHAR MAY
Then comes our query, which is as follows.
What I mean is, Select all the columns you need , But you have to order by the
Column which you can use to find out the order of the records.
As you want to order by the date, I am ordering by the MYDATE DESC.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 3 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
17-MAY-00 GIRI DHAR MAY
Just to test whether it works for the Recent 2 records, I tried this query too.
SQL> select MYDATE,NAME1,NAME2,NAME3
2 from TEST a
3 where 2 > (select count(*)
4 from TEST b
5 where b.MYDATE > a.MYDATE) order by 1 desc;
MYDATE NAME1 NAME2 NAME3
--------- -------------------- -------------------- --------------------
03-AUG-00 W R E
18-JUL-00 WEWE GHH FDFD
Hope this is clear. Pls let me know if this helps you.
Cheers,
Giridhar Kodakalla
|
|
|
|
| Re: SQL [message #373944 is a reply to message #373856] |
Thu, 17 May 2001 15:33 |
Sss
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Both of you sound like null and neel from RAMAYAN
or SEETA aur GEETA.
STUPIDS
|
|
|
|
| Re: SQL [message #373945 is a reply to message #373856] |
Thu, 17 May 2001 15:34 |
Sss
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Both of you sound like null and neel from RAMAYAN
or SEETA aur GEETA.
STUPIDS
|
|
|
|
| Re: SQL [message #373946 is a reply to message #373856] |
Thu, 17 May 2001 15:35 |
Sss
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Both of you sound like null and neel from RAMAYAN
or SEETA aur GEETA.
STUPIDS
|
|
|
|
| Re: SQL [message #373947 is a reply to message #373856] |
Thu, 17 May 2001 15:53 |
Sss
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Both of you sound like null and neel from RAMAYAN
or SEETA aur GEETA.
STUPIDS
|
|
|
|
| Re: SQL [message #373948 is a reply to message #373856] |
Thu, 17 May 2001 15:54 |
Sss
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Both of you sound like null and neel from RAMAYAN
or SEETA aur GEETA.
STUPIDS
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 17:02:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
