help with SQL Select Statement

help with SQL Select Statement

Post by smag.. » Sun, 31 Dec 1899 09:00:00



Hi,

I have a emp table which has data as follows:

Serial No.      Start           Stop
1               11-SEP-85
2               12-JAN-86
3               18-FEB-86
4               18-FEB-86       20-Mar-86
5               25-MAY-86
6               11-NOV-86
7               11-NOV-86       12-NOV-86
8               22-MAR-87
9               12-SEP-89

I want to write a SQL query which retrives:

Serial No.      Start           Stop
1               11-SEP-85
2               12-JAN-86
4               18-FEB-86       20-Mar-86
5               25-MAY-86
7               11-NOV-86       12-NOV-86
8               22-MAR-87
9               12-SEP-89

As shown in the example above if there are two start dates which are
same, it should retrive the row with the stop date.

Any help would be appreciated.

Thank you

sashi

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

help with SQL Select Statement

Post by Karen Abgaria » Sun, 31 Dec 1899 09:00:00


Looks ugly, but this is because the task itself is against SQL's nature:

select no#, a.start#, decode( cnt#, 2, stop#, null )
from (select no#, start#, stop# from tt) a,
(select START#, count(*) cnt# from tt group by start#) b
where a.start# = b.start# and not (stop# is null and cnt#=2);

Maybe there are better ways, but since you asked for help...

Regards,
Karen Abgarian.


> Hi,

> I have a emp table which has data as follows:

> Serial No.      Start           Stop
> 1               11-SEP-85
> 2               12-JAN-86
> 3               18-FEB-86
> 4               18-FEB-86       20-Mar-86
> 5               25-MAY-86
> 6               11-NOV-86
> 7               11-NOV-86       12-NOV-86
> 8               22-MAR-87
> 9               12-SEP-89

> I want to write a SQL query which retrives:

> Serial No.      Start           Stop
> 1               11-SEP-85
> 2               12-JAN-86
> 4               18-FEB-86       20-Mar-86
> 5               25-MAY-86
> 7               11-NOV-86       12-NOV-86
> 8               22-MAR-87
> 9               12-SEP-89

> As shown in the example above if there are two start dates which are
> same, it should retrive the row with the stop date.

> Any help would be appreciated.

> Thank you

> sashi

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.


 
 
 

help with SQL Select Statement

Post by Jurij Mod » Sun, 31 Dec 1899 09:00:00



>Hi,

>I have a emp table which has data as follows:
>....[SNIP]....

>As shown in the example above if there are two start dates which are
>same, it should retrive the row with the stop date.

>Any help would be appreciated.

>Thank you

>sashi

The simplest (yet maybe not the fastest) way would be:

SELECT serial_no, start, stop FROM emp
WHERE serial_no IN (SELECT MAX(serial_no) FROM emp GROUP BY start);

HTH,


Certified Oracle DBA (7.3 & 8.0 OCP)
================================================
The above opinions are mine and do not represent
any official standpoints of my employer

 
 
 

help with SQL Select Statement

Post by Frank Huben » Sun, 31 Dec 1899 09:00:00


The following are a couple more solutions to add to the collection:

SQL> desc emp
 Name                               Null?    Type
 ---------------------------------- -------- ----------
 SERIAL_NO                                   NUMBER
 STARTD                                      DATE
 STOPD                                       DATE

SQL> select * from emp;

SERIAL_NO STARTD    STOPD
--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        3 18-FEB-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        6 11-NOV-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

9 rows selected.

SQL> select serial_no, startd, stopd
  2    from emp
  3  minus
  4  select serial_no, startd, stopd
  5    from emp b
  6   where stopd is null
  7     and exists (select startd from emp where stopd is not null and
startd = b.startd);

SERIAL_NO STARTD    STOPD
--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

7 rows selected.

SQL> select a.serial_no, a.startd, a.stopd
  2    from emp a,
  3         (select startd, max(stopd) m from emp group by startd) b
  4   where a.startd = b.startd
  5     and ((a.stopd = b.m) or (a.stopd is null and b.m is null))
  6   order by 2;

SERIAL_NO STARTD    STOPD
--------- --------- ---------
        1 11-SEP-85
        2 12-JAN-86
        4 18-FEB-86 20-MAR-86
        5 25-MAY-86
        7 11-NOV-86 12-NOV-86
        8 22-MAR-87
        9 12-SEP-89

7 rows selected.


> Hi,

> I have a emp table which has data as follows:

> Serial No.      Start           Stop
> 1               11-SEP-85
> 2               12-JAN-86
> 3               18-FEB-86
> 4               18-FEB-86       20-Mar-86
> 5               25-MAY-86
> 6               11-NOV-86
> 7               11-NOV-86       12-NOV-86
> 8               22-MAR-87
> 9               12-SEP-89

> I want to write a SQL query which retrives:

> Serial No.      Start           Stop
> 1               11-SEP-85
> 2               12-JAN-86
> 4               18-FEB-86       20-Mar-86
> 5               25-MAY-86
> 7               11-NOV-86       12-NOV-86
> 8               22-MAR-87
> 9               12-SEP-89

> As shown in the example above if there are two start dates which are
> same, it should retrive the row with the stop date.

> Any help would be appreciated.

> Thank you

> sashi

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

 
 
 

help with SQL Select Statement

Post by AleX » Sun, 31 Dec 1899 09:00:00


how about this (replace c1, c2, c3 with the appropriate column names)

select c1, c2, to_date (null) from emp where c2 not in
(select a.c2 from emp a, emp b
where a.c1=b.c1 and a.c2=b.c2 and b.c3 is not null)
union
select c1, c2, c3 from emp where c3 is not null



> Hi,

> I have a emp table which has data as follows:

> Serial No. Start           Stop
> 1          11-SEP-85
> 2          12-JAN-86
> 3          18-FEB-86
> 4          18-FEB-86       20-Mar-86
> 5          25-MAY-86
> 6          11-NOV-86
> 7          11-NOV-86       12-NOV-86
> 8          22-MAR-87
> 9          12-SEP-89

> I want to write a SQL query which retrives:

> Serial No. Start           Stop
> 1          11-SEP-85
> 2          12-JAN-86
> 4          18-FEB-86       20-Mar-86
> 5          25-MAY-86
> 7          11-NOV-86       12-NOV-86
> 8          22-MAR-87
> 9          12-SEP-89

> As shown in the example above if there are two start dates which are
> same, it should retrive the row with the stop date.

> Any help would be appreciated.

> Thank you

> sashi

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
                 Alex Shterenberg
"I hate people. I think they should suffer as much as
 possible, and therefore I'm into those old communist
         dictatorships."  - Euronymous, Mayhem

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

1. NEED HELP WITH SQL SELECT STATEMENT - need to reference cookie info

ok...  what I am trying to do is have my select statement query a
certain field by the value in a cookie.  I cannot figure out how to
have the SELECT statement reference the cookie (ie.
request.cookies("cookiename").

Is there a way to do it within the SELECT statement?  Below where it
states **COOKIE VALUE** is where I need it to pull the cookie value.

Here is the code:

sql="select " & strdistinct & " " & strproductfields
        sql=sql & " from products p, prodcategories cc, categories c"
        sql=sql & " where cc.intcatalogid=p.catalogid and
cc.intcategoryid=c.categoryid and"
        sql=sql & " p.pother1=**COOKIE VALUE** and"
        if cat_id <> "" then
             sql = sql & " cc.intcategoryid = " & cat_id

I appreciate any info!
THANKS!

2. ADO Permissions

3. HELP WITH SQL SELECT STATEMENT

4. Trying to create a simple view against system view. Don't think possible

5. Retrieving the GUID

6. Need help with SQL SELECT statement

7. Help - Complex SQL SELECT statement!!!!

8. Help me convert a SELECT statement to an UPDATE statement

9. USING A FIELD FROM MAIN SELECT STATEMENT IN SUB SELECT STATEMENT

10. Customer Statement SQL Select statement???

11. SQL Gurus: Need help with simple SELECT Statement