| SQL query syntax [message #373860] |
Wed, 16 May 2001 03:26  |
Sarah
Messages: 15
Registered: June 1999
|
Junior Member |
|
|
Hi,
I wonder if anyone can help me on this query.
I am trying to find the higher number in a chain for each of the chains existing in a table.
The relevant table rows are:
Chain No. Chain sequence
============================
1 ............... 1
1 ............... 2
1 ............... 3
2 ............... 1
2 ............... 2
the result I expect is:
chain no. chain sequence
1 ............... 3
2 ............... 2
I tried the following query:
select distinct chain_n, (select max(chain_id) from table_name ) from table_name;
but did not get the results I need. Instead I got this:
chain no. chain sequence
1 ............... 3
2 ............... 3
Can anyone help me with this query syntax?
Thanks, Sarah
|
|
|
|
| Re: SQL query syntax [message #373861 is a reply to message #373860] |
Wed, 16 May 2001 03:34  |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am sending the details of my query which fetches the details in the manner you require.
SQL> create table chain (chain_no number,chain_sequence number);
Table created.
SQL> insert into chain values (1,1);
1 row created.
SQL> insert into chain values (1,2);
1 row created.
SQL> insert into chain values (1,3);
1 row created.
SQL> insert into chain values (2,1);
1 row created.
SQL> insert into chain values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from chain;
CHAIN_NO CHAIN_SEQUENCE
--------- --------------
1 1
1 2
1 3
2 1
2 2
SQL> select chain_no,count(chain_sequence) from chain group by chain_no order by
count(chain_sequence) desc
CHAIN_NO COUNT(CHAIN_SEQUENCE)
--------- ---------------------
1 3
2 2
Hope This Helps.
Cheers,
Giridhar
|
|
|
|
| Re: SQL query syntax [message #373862 is a reply to message #373860] |
Wed, 16 May 2001 03:36 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am sending the details of my query which fetches the details in the manner you require.
SQL> create table chain (chain_no number,chain_sequence number);
Table created.
SQL> insert into chain values (1,1);
1 row created.
SQL> insert into chain values (1,2);
1 row created.
SQL> insert into chain values (1,3);
1 row created.
SQL> insert into chain values (2,1);
1 row created.
SQL> insert into chain values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from chain;
CHAIN_NO CHAIN_SEQUENCE
--------- --------------
1 1
1 2
1 3
2 1
2 2
SQL> select chain_no,count(chain_sequence) from chain group by chain_no order by
count(chain_sequence) desc
CHAIN_NO COUNT(CHAIN_SEQUENCE)
--------- ---------------------
1 3
2 2
Hope This Helps.
Cheers,
Giridhar
|
|
|
|
| Re: SQL query syntax [message #373863 is a reply to message #373860] |
Wed, 16 May 2001 03:38 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am sending the details of my query which fetches the details in the manner you require.
SQL> create table chain (chain_no number,chain_sequence number);
Table created.
SQL> insert into chain values (1,1);
1 row created.
SQL> insert into chain values (1,2);
1 row created.
SQL> insert into chain values (1,3);
1 row created.
SQL> insert into chain values (2,1);
1 row created.
SQL> insert into chain values (2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from chain;
CHAIN_NO CHAIN_SEQUENCE
--------- --------------
1 1
1 2
1 3
2 1
2 2
SQL> select chain_no,count(chain_sequence) from chain group by chain_no order by
count(chain_sequence) desc
CHAIN_NO COUNT(CHAIN_SEQUENCE)
--------- ---------------------
1 3
2 2
Hope This Helps.
Cheers,
Giridhar
|
|
|
|
| Re: SQL query syntax [message #373864 is a reply to message #373860] |
Wed, 16 May 2001 03:41 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am sending the details of my query which fetches the details in the manner you require.
SQL> create table chain (chain_no number,chain_sequence number);
Table created.
SQL> insert into chain values (1,1);
SQL> insert into chain values (1,2);
SQL> insert into chain values (1,3);
SQL> insert into chain values (2,1);
SQL> insert into chain values (2,2);
SQL> commit;
SQL> select * from chain;
CHAIN_NO CHAIN_SEQUENCE
--------- --------------
1 1
1 2
1 3
2 1
2 2
SQL> select chain_no,count(chain_sequence) from chain group by chain_no order by
count(chain_sequence) desc
CHAIN_NO COUNT(CHAIN_SEQUENCE)
--------- ---------------------
1 3
2 2
Hope This Helps.
Cheers,
Giridhar
|
|
|
|
| Re: SQL query syntax [message #373865 is a reply to message #373860] |
Wed, 16 May 2001 04:40 |
GIRIDHAR KODAKALLA
Messages: 92
Registered: May 2001
|
Member |
|
|
Hai,
I am sending the details of my query which fetches the details in the manner you require.
SQL> create table chain (chain_no number,chain_sequence number);
Table created.
SQL> insert into chain values (1,1);
SQL> insert into chain values (1,2);
SQL> insert into chain values (1,3);
SQL> insert into chain values (2,1);
SQL> insert into chain values (2,2);
SQL> commit;
SQL> select * from chain;
CHAIN_NO CHAIN_SEQUENCE
--------- --------------
1 1
1 2
1 3
2 1
2 2
SQL> select chain_no,count(chain_sequence) from chain group by chain_no order by
count(chain_sequence) desc
CHAIN_NO COUNT(CHAIN_SEQUENCE)
--------- ---------------------
1 3
2 2
Hope This Helps.
Cheers,
Giridhar
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
