Home » SQL & PL/SQL » SQL & PL/SQL » regarding query grouping
regarding query grouping [message #199008] Thu, 19 October 2006 11:05 Go to next message
rajuakula
Messages: 63
Registered: March 2005
Member
I have a table with the following data

id columnname value date
----------------------------------------------------------------------
12 sal 2000 12-Oct-2006 10:30:21 AM
12 design clerk 12-Oct-2006 10:30:21 AM
12 location Wellington 12-Oct-2006 10:30:21 AM
12 rdate 12-Oct-2006 10:30:21 AM 12-Oct-2006 10:30:21 AM
12 sal 3000 15-Oct-2006 10:45:26 AM
12 location Jeff st 15-Oct-2006 10:45:26 AM
12 rdate 15-Oct-2006 10:45:26 AM 15-Oct-2006 10:45:26 AM
12 sal 2500 17-Nov-2006 11:33:47AM
12 location Thomas St 17-Nov-2006 11:33:47 AM



Now I need a query with data below using above table. If not possible with a single query then stored proc also no probs.

12 sal 3000 15-Oct-2006 10:45:26 AM
12 design clerk 12-Oct-2006 10:30:21 AM
12 location Jeff st 15-Oct-2006 10:45:26 AM
12 rdate 12-Oct-2006 10:30:21 AM 12-Oct-2006 10:30:21 AM


thanks in advance

Re: regarding query grouping [message #199011 is a reply to message #199008] Thu, 19 October 2006 11:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And by what logic do you reach that result?

From your description,

select * from table where
value in ('2000', 'clerk', 'Jeff st', '12-Oct-2006')


will fit your requirement.

Also, Can you repost that with *CODE* Tags, so we can actually SEE what data is in what column.
Re: regarding query grouping [message #199012 is a reply to message #199008] Thu, 19 October 2006 11:34 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
I will pass only id as the parameter.
I am sending it as an attachment.

thanks.
  • Attachment: file.txt
    (Size: 1.13KB, Downloaded 209 times)
Re: regarding query grouping [message #199025 is a reply to message #199012] Thu, 19 October 2006 12:48 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ok, here is it with CODE Tags :

Table :
id    columnname   value                      date
----------------------------------------------------------------------
12    sal          2000                         12-Oct-2006 10:30:21 AM
12    design       clerk                        12-Oct-2006 10:30:21 AM
12    location     Wellington                   12-Oct-2006 10:30:21 AM
12    rdate        12-Oct-2006 10:30:21 AM      12-Oct-2006 10:30:21 AM
12    sal          3000                         15-Oct-2006 10:45:26 AM
12    location     Jeff st                      15-Oct-2006 10:45:26 AM
12    rdate        15-Oct-2006 10:45:26 AM      15-Oct-2006 10:45:26 AM
12    sal          2500                         17-Nov-2006 11:33:47AM
12    location     Thomas St                    17-Nov-2006 11:33:47 AM



Result :

id    columnname   value                      date
----------------------------------------------------------------------
12      sal         3000                        15-Oct-2006 10:45:26 AM
12      design      clerk                       12-Oct-2006 10:30:21 AM
12      location    Jeff st                     15-Oct-2006 10:45:26 AM
12      rdate       12-Oct-2006 10:30:21 AM     12-Oct-2006 10:30:21 AM


And as I said, you can get that result by running

select * from table where
value in ('2000', 'clerk', 'Jeff st', '12-Oct-2006')


Is that what you want?

If not, what is it that you want?

The rows whith the highest 'value' grouped by 'columnname'? Can't be, 3000 is the higest value for 'sal', but 'Jeff st' is NOT the highest value for 'location'.

The rows with the lowest date per group? can't be, then the the SAL group should be the 2000 one from 12-Oct.

The rows with the highest date per group? can't be, then the the LOCATION group should be the 'Thomas St' one from 17-Nov-2006

So WHAT do you want? If you can't tell us what you want, we can't help you.
Re: regarding query grouping [message #199123 is a reply to message #199008] Fri, 20 October 2006 05:35 Go to previous messageGo to next message
rajuakula
Messages: 63
Registered: March 2005
Member
Hi ,
plz. see the result of the second table.

I need the value of each columnname which has date less the highest date.like below
look at columnname data (sal's value and date)
same way for columnname data(location's value and date)
I should retrieve the second highest date's row with id,columnname and value.If the table1 does not contain a row for the second highest then I need to retrieve the value of the single row as for the columnname data(design's value and date).

id columnname value date
----------------------------------------------------------------------
12 sal 3000 15-Oct-2006 10:45:26 AM
12 design clerk 12-Oct-2006 10:30:21 AM
12 location Jeff st 15-Oct-2006 10:45:26 AM
12 rdate 12-Oct-2006 10:30:21 AM 12-Oct-2006 10:30:21 AM



thanks
Re: regarding query grouping [message #199134 is a reply to message #199123] Fri, 20 October 2006 07:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, that would have been quite difficult to figure out by just looking at the result.

In that case It could be done with rank().

Thist would be a solution :

SQL> -- create table
SQL> CREATE TABLE test (
  2    id         NUMBER,
  3    colname    VARCHAR2(10),
  4    colval     VARCHAR2(10),
  5    coldate    DATE
  6  );

Table created.

SQL>
SQL>
SQL> --INSERT test date
SQL> INSERT INTO test VALUES (12, '01','01',SYSDATE - 0);

1 row created.

SQL> INSERT INTO test VALUES (12, '02','02',SYSDATE - 1);

1 row created.

SQL> INSERT INTO test VALUES (12, '03','03',SYSDATE - 2);

1 row created.

SQL> INSERT INTO test VALUES (12, '01','04',SYSDATE - 3);

1 row created.

SQL> INSERT INTO test VALUES (12, '02','05',SYSDATE - 4);

1 row created.

SQL> INSERT INTO test VALUES (12, '03','06',SYSDATE - 5);

1 row created.

SQL> INSERT INTO test VALUES (12, '01','07',SYSDATE - 6);

1 row created.

SQL> INSERT INTO test VALUES (12, '02','08',SYSDATE - 7);

1 row created.

SQL> INSERT INTO test VALUES (12, '03','09',SYSDATE - 8);

1 row created.

SQL> INSERT INTO test VALUES (12, '01','10',SYSDATE - 9);

1 row created.

SQL> INSERT INTO test VALUES (12, '02','11',SYSDATE - 10);

1 row created.

SQL> INSERT INTO test VALUES (12, '03','12',SYSDATE - 11);

1 row created.

SQL> INSERT INTO test VALUES (12, '04','13',SYSDATE - 12);

1 row created.

SQL>
SQL>
SQL> -- select table data
SQL> SELECT * FROM test;

        ID COLNAME    COLVAL     COLDATE
---------- ---------- ---------- ---------
        12 01         01         20-OCT-06
        12 02         02         19-OCT-06
        12 03         03         18-OCT-06
        12 01         04         17-OCT-06
        12 02         05         16-OCT-06
        12 03         06         15-OCT-06
        12 01         07         14-OCT-06
        12 02         08         13-OCT-06
        12 03         09         12-OCT-06
        12 01         10         11-OCT-06
        12 02         11         10-OCT-06

        ID COLNAME    COLVAL     COLDATE
---------- ---------- ---------- ---------
        12 03         12         09-OCT-06
        12 04         13         08-OCT-06

13 rows selected.

SQL>
SQL> -- add a rank for date
SQL> SELECT id,
  2         colname,
  3         colval,
  4         coldate,
  5         Rank() over ( PARTITION BY id, colname ORDER BY coldate desc) date_rank_desc
  6  FROM test;

        ID COLNAME    COLVAL     COLDATE   DATE_RANK_DESC
---------- ---------- ---------- --------- --------------
        12 01         01         20-OCT-06              1
        12 01         04         17-OCT-06              2
        12 01         07         14-OCT-06              3
        12 01         10         11-OCT-06              4
        12 02         02         19-OCT-06              1
        12 02         05         16-OCT-06              2
        12 02         08         13-OCT-06              3
        12 02         11         10-OCT-06              4
        12 03         03         18-OCT-06              1
        12 03         06         15-OCT-06              2
        12 03         09         12-OCT-06              3

        ID COLNAME    COLVAL     COLDATE   DATE_RANK_DESC
---------- ---------- ---------- --------- --------------
        12 03         12         09-OCT-06              4
        12 04         13         08-OCT-06              1

13 rows selected.

SQL>
SQL> -- select only rank 1 and 2
SQL> SELECT * FROM (
  2      SELECT id,
  3            colname,
  4             colval,
  5             coldate,
  6             Rank() over ( PARTITION BY id, colname ORDER BY coldate desc) date_rank_desc
  7      FROM test )
  8      WHERE date_rank_desc < 3;

        ID COLNAME    COLVAL     COLDATE   DATE_RANK_DESC
---------- ---------- ---------- --------- --------------
        12 01         01         20-OCT-06              1
        12 01         04         17-OCT-06              2
        12 02         02         19-OCT-06              1
        12 02         05         16-OCT-06              2
        12 03         03         18-OCT-06              1
        12 03         06         15-OCT-06              2
        12 04         13         08-OCT-06              1

7 rows selected.

SQL>
SQL> -- add a reversed rank to get the earliest of the two latest dates
SQL> SELECT id,
  2         colname,
  3         colval,
  4         coldate,
  5         date_rank_desc,
  6         Rank() over ( PARTITION BY id, colname ORDER BY coldate asc) date_rank_asc
  7    FROM (
  8        SELECT id,
  9              colname,
 10               colval,
 11               coldate,
 12               Rank() over ( PARTITION BY id, colname ORDER BY coldate desc) date_rank_desc
 13        FROM test )
 14    WHERE date_rank_desc < 3;

        ID COLNAME    COLVAL     COLDATE   DATE_RANK_DESC DATE_RANK_ASC
---------- ---------- ---------- --------- -------------- -------------
        12 01         04         17-OCT-06              2             1
        12 01         01         20-OCT-06              1             2
        12 02         05         16-OCT-06              2             1
        12 02         02         19-OCT-06              1             2
        12 03         06         15-OCT-06              2             1
        12 03         03         18-OCT-06              1             2
        12 04         13         08-OCT-06              1             1

7 rows selected.

SQL>
SQL> -- And then the final Query : Get only the earliest of the two latest dates :
SQL> SELECT * FROM (
  2      SELECT id,
  3             colname,
  4             colval,
  5             coldate,
  6             date_rank_desc,
  7             Rank() over ( PARTITION BY id, colname ORDER BY coldate asc) date_rank_asc
  8        FROM (
  9            SELECT id,
 10                  colname,
 11                   colval,
 12                   coldate,
 13                   Rank() over ( PARTITION BY id, colname ORDER BY coldate desc) date_rank_desc
 14            FROM test )
 15        WHERE date_rank_desc < 3
 16  ) WHERE date_rank_asc = 1 ;

        ID COLNAME    COLVAL     COLDATE   DATE_RANK_DESC DATE_RANK_ASC
---------- ---------- ---------- --------- -------------- -------------
        12 01         04         17-OCT-06              2             1
        12 02         05         16-OCT-06              2             1
        12 03         06         15-OCT-06              2             1
        12 04         13         08-OCT-06              1             1

SQL>
SQL> -- clean up
SQL> drop TABLE test;

Table dropped.

SQL>


Hope that helps.
Re: regarding query grouping [message #199472 is a reply to message #199134] Tue, 24 October 2006 11:06 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Just a thought

select id, colname, colval, coldate from (
select id, colname, colval, coldate, rank() over(partition by colname order by coldate desc) as rank_val,
count(*) over(partition by colname) as col_count from test)
where rank_val = 2 or col_count = 1

Previous Topic: DBMS JOB SCHEDULING
Next Topic: Getting STATMENT CODE OF A TABLE
Goto Forum:
  


Current Time: Wed Dec 07 03:11:32 CST 2016

Total time taken to generate the page: 0.09223 seconds