Home » SQL & PL/SQL » SQL & PL/SQL » regarding query grouping
| regarding query grouping [message #199008] |
Thu, 19 October 2006 11:05  |
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  |
ThomasG
Messages: 3211
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 #199025 is a reply to message #199012] |
Thu, 19 October 2006 12:48 |
ThomasG
Messages: 3211
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 |
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 |
ThomasG
Messages: 3211
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 |
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
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 10:39:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
