SQL query. Order by place 
Author Message
 SQL query. Order by place

Hi,

I have got a table with peolpe names, IDs and point.
For examle

ID   NAME   POINTS
------------------
1    XY     20
2    ZV     2
3    AB     10
4    CD     10
5    EF     5
6    GH     2

I'd like to make a querie that lists all of the
people order by point and wiht place number:

PLACE  ID  NAME PONITS
----------------------
1      1   XY   20
2      3   AB   10
2      4   CD   10
4      5   EF   5
5      6   GH   2
5      2   ZV   2

Thanks for any help

Bela



Fri, 02 May 2003 03:00:00 GMT
 SQL query. Order by place

See rank in the OLAP functions in the SQL Reference:

select rank() over (order by points desc) as place,id,name,points from
standing

Quote:

> Hi,

> I have got a table with peolpe names, IDs and point.
> For examle

> ID   NAME   POINTS
> ------------------
> 1    XY     20
> 2    ZV     2
> 3    AB     10
> 4    CD     10
> 5    EF     5
> 6    GH     2

> I'd like to make a querie that lists all of the
> people order by point and wiht place number:

> PLACE  ID  NAME PONITS
> ----------------------
> 1      1   XY   20
> 2      3   AB   10
> 2      4   CD   10
> 4      5   EF   5
> 5      6   GH   2
> 5      2   ZV   2

> Thanks for any help

> Bela




Fri, 02 May 2003 03:00:00 GMT
 SQL query. Order by place
Hi,
The RANK() OVER() olap functions do the trick:
    select rank() over (order by point desc) as place,id,name,point from
people

Check out the sql refr for more details.
Hope this helps,
David.

Quote:

> Hi,

> I have got a table with peolpe names, IDs and point.
> For examle

> ID   NAME   POINTS
> ------------------
> 1    XY     20
> 2    ZV     2
> 3    AB     10
> 4    CD     10
> 5    EF     5
> 6    GH     2

> I'd like to make a querie that lists all of the
> people order by point and wiht place number:

> PLACE  ID  NAME PONITS
> ----------------------
> 1      1   XY   20
> 2      3   AB   10
> 2      4   CD   10
> 4      5   EF   5
> 5      6   GH   2
> 5      2   ZV   2

> Thanks for any help

> Bela




Fri, 02 May 2003 03:00:00 GMT
 SQL query. Order by place

Bela:

I believe recursive SQL could do this for you.  I'll look into it - I've
love making complex queries anyway and haven't messed with recursive stuff
much on DB2 yet.  The quick and dirty way, which I bet you already know, is
to order the recordset and use the application to write the place column:

SELECT ID, NAME, POINTS FROM PTS ORDER BY POINTS

Iterate through the recordset returned incrementing a counter for the place
column.

Hope this works until me or someone can help you with the advanced SQL...

Bill
Programmer/Analyst
Insituform Technologies
St. Louis, Missouri



                                         cc:                                                                  
                    11/13/2000           Subject:     SQL query. Order by place                              
                    08:27 AM                                                                                  
                    Please                                                                                    
                    respond to                                                                                
                    comp.database                                                                            
                    s.ibm-db2                                                                                

Hi,

I have got a table with peolpe names, IDs and point.
For examle

ID   NAME   POINTS
------------------
1    XY     20
2    ZV     2
3    AB     10
4    CD     10
5    EF     5
6    GH     2

I'd like to make a querie that lists all of the
people order by point and wiht place number:

PLACE  ID  NAME PONITS
----------------------
1      1   XY   20
2      3   AB   10
2      4   CD   10
4      5   EF   5
5      6   GH   2
5      2   ZV   2

Thanks for any help

Bela

 Sent via Deja.com http://www.deja.com/
 Before you buy.



Tue, 06 May 2003 03:00:00 GMT
 SQL query. Order by place
This can be done with simple subselect like the next "(select
count(id) from table st where st.points > t.points) + 1". Exactly what
he wants.  ("t" is outer table instance)
Quote:

> Bela:

> I believe recursive SQL could do this for you.  I'll look into it - I've
> love making complex queries anyway and haven't messed with recursive stuff
> much on DB2 yet.  The quick and dirty way, which I bet you already know, is
> to order the recordset and use the application to write the place column:

> SELECT ID, NAME, POINTS FROM PTS ORDER BY POINTS

> Iterate through the recordset returned incrementing a counter for the place
> column.

> Hope this works until me or someone can help you with the advanced SQL...

> Bill
> Programmer/Analyst
> Insituform Technologies
> St. Louis, Missouri



>                                          cc:                                                                  
>                     11/13/2000           Subject:     SQL query. Order by place                              
>                     08:27 AM                                                                                  
>                     Please                                                                                    
>                     respond to                                                                                
>                     comp.database                                                                            
>                     s.ibm-db2                                                                                

> Hi,

> I have got a table with peolpe names, IDs and point.
> For examle

> ID   NAME   POINTS
> ------------------
> 1    XY     20
> 2    ZV     2
> 3    AB     10
> 4    CD     10
> 5    EF     5
> 6    GH     2

> I'd like to make a querie that lists all of the
> people order by point and wiht place number:

> PLACE  ID  NAME PONITS
> ----------------------
> 1      1   XY   20
> 2      3   AB   10
> 2      4   CD   10
> 4      5   EF   5
> 5      6   GH   2
> 5      2   ZV   2

> Thanks for any help

> Bela

>  Sent via Deja.com http://www.deja.com/
>  Before you buy.



Mon, 12 May 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. DevWizard 2001 Version 2.0 - Place your order now!

2. DevWizard 2001 Version 2.0 - Place your order now!

3. DevWizard 2001 Version 2.0 - Place your order now!

4. Retrieving records in order they were placed.

5. Retrieving records in order they were placed.

6. DevWizard 2001 Version 2.0 - Place your order now!

7. Query results order w/o order by clause

8. Results order same as query order

9. Sort Order in Query without Order By

10. Ranking (NOT ordering) in an SQL query.

11. sql query - items not in an order...

12. Decimal places in select query


 
Powered by phpBB® Forum Software