 | |
finding last date
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
finding last date -
05-23-2007
, 01:14 PM
#2
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
and that might mean admin and not sql stuff.
Robert
#3
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
What about
select name
, max(date) as max_date
from table_name_voy_date
group by name
another one could be:
select t2.name, t2.date
from (select name, max(voy) as max_voy from table_name_voy_date group
by name) t1
join table_name_voy_date t2
on t1.name = t2.name
and t1.max_voy = t2.voy
Please be aware that date is a reserved oracle word, so better name it
something different.
If this helps, I recommend you buy a good book on basic SQL. If not,
give us some more clues about what the challenge is.
Regards,
Erik Ykema
#4
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
What about
select name
, max(date) as max_date
from table_name_voy_date
group by name
another one could be:
select t2.name, t2.date
from (select name, max(voy) as max_voy from table_name_voy_date group
by name) t1
join table_name_voy_date t2
on t1.name = t2.name
and t1.max_voy = t2.voy
Please be aware that date is a reserved oracle word, so better name it
something different.
If this helps, I recommend you buy a good book on basic SQL. If not,
give us some more clues about what the challenge is.
Regards,
Erik Ykema
#5
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
DATE is a datatype and a reserved word in Oracle. Second, if you
can't use GROUP BY to get these results you should probably take a
refresher course in SQL. This is a simple query to write:
select name, voy, mydate
from mytable
where (name, mydate) in (select name, max(mydate) from mytable group
by name);
I presume since your example is oversimplified the query I just
supplied won't work without severe modification. Such is the price
you pay for not providing accurate details regarding what you are
trying to do.
David Fitzjarrell
#6
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
Quote:
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
What about
select name
, max(date) as max_date
from table_name_voy_date
group by name
another one could be:
select t2.name, t2.date
from (select name, max(voy) as max_voy from table_name_voy_date group
by name) t1
join table_name_voy_date t2
on t1.name = t2.name
and t1.max_voy = t2.voy
Please be aware that date is a reserved oracle word, so better name it
something different.
If this helps, I recommend you buy a good book on basic SQL. If not,
give us some more clues about what the challenge is.
Regards,
Erik Ykema
#7
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
from foo f1
where date =
(select max(date)
from foo f2
where f2.name = f1.name
)
You don't want to know how often this question have been asked.
Banging should be replaced by a compusory search of the archives.
--
Sybrand Bakker
Senior Oracle DBA
#8
| |||
| |||
|
|
I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert |
SELECT NAME,VOY,SOME_DATE,
MAX(SOME_DATE) OVER ( PARTITION BY NAME) AS LAST_DATE
FROM TABLE )
select NAME,VOY,SOME_DATE
from MISHMASH
where SOME_DATE=LAST_DATE;
If the "VOY" column is not required, then the query is much
simpler:
select name,max(some_date)
from table
group by name;
This is really elementary SQL. Please, do us all a favor
and sto using this group as a free CBT. It's annoying as
heck and denigrating to the people like me who did invest
the time and effort to learn things.
--
http://www.mladen-gogala.com
#9
| |||
| |||
|
|
you pay for not providing accurate details regarding what you are trying to do. |
- --
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
iD8DBQFGVIydLw8L4IAs830RAtRyAJ9W0zN79YKLXsiIMhBQwP qvo0DayACeKfgr
CrBJwoWmU88XhjmI7NdaaLM=
=AYxg
-----END PGP SIGNATURE-----
#10
| |||
| |||
|
|
On May 23, 1:14 pm, Robert Hicks <sigz... (AT) gmail (DOT) com> wrote: I have a table (simplified): NAM E VOY DATE The NAME column can have the same entry up to 10 times based on the VOY. So it could look something like this: NAME VOY DATE 123456 0 <some date 123456 1 <some date 345677 0 <some date 345677 1 <some date 345677 2 <some date 345677 3 <some date 098766 0 <some date Some of the NAME items have VOYS up to 9 but not all of them do. I need to pull out the NAME and the LAST <some date> for each "distinct" NAME. So in the above case I would get: 123456 1 <some date 345677 3 <some date 098766 0 <some date The <some date> is always later as the VOY increments up. Any help would be appreciated. I have been banging my head all day. Robert First if DATE is an actual column name you need to change it since DATE is a datatype and a reserved word in Oracle. Second, if you can't use GROUP BY to get these results you should probably take a refresher course in SQL. This is a simple query to write: select name, voy, mydate from mytable where (name, mydate) in (select name, max(mydate) from mytable group by name); I presume since your example is oversimplified the query I just supplied won't work without severe modification. Such is the price you pay for not providing accurate details regarding what you are trying to do. David Fitzjarrell |
was just trying to simplify the explanation. The column names are
actually LLY_NUM, VOY_NUM, TP_DATE.
I will try what you have.
Robert
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.