Home » SQL & PL/SQL » SQL & PL/SQL » Middle Most Record in a Table
Middle Most Record in a Table [message #184081] Tue, 25 July 2006 04:36 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi,

I want to get the middle most record in my table.
Pl. suggest how to get that..

Anil
Re: Middle Most Record in a Table [message #184085 is a reply to message #184081] Tue, 25 July 2006 04:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please define middle most.
Is it based on time of insert or some field value?
How to handle duplicate values of your field in different records?
What if there are an even number of records?

Hint: search this site for top n records to get an idea of how to start.
Re: Middle Most Record in a Table [message #184086 is a reply to message #184081] Tue, 25 July 2006 04:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What if your table has an even number of rows?
Which of the two middle rows would you like?

Other than that, it's just a 'Get the Nth row' question with the slight twist that you don't know what N is.

SQL> create table temp_middle (id  number, col_1  varchar2(10));

Table created.

SQL> insert into temp_middle (select rnum,'Value '||rnum from (select level rnum from dual connect by level <11));

10 rows created.

SQL> select * from temp_middle;

        ID COL_1
---------- ----------
         1 Value 1
         2 Value 2
         3 Value 3
         4 Value 4
         5 Value 5
         6 Value 6
         7 Value 7
         8 Value 8
         9 Value 9
        10 Value 10

10 rows selected.

SQL> select id,col_1 
  2  from (select id
  3              ,col_1
  4              ,row_number() over (order by id desc) rmax
  5              ,row_number() over (order by id)      rmin
  6              ,rownum rnum
  7        from   temp_middle t
  8        order by id)
  9  where rnum = floor(rmax/2 + rmin/2);

        ID COL_1
---------- ----------
         5 Value 5
Re: Middle Most Record in a Table [message #184087 is a reply to message #184081] Tue, 25 July 2006 05:00 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Thank you very much this is the one I am looking for.

Regards,

Anil
Re: Middle Most Record in a Table [message #184102 is a reply to message #184086] Tue, 25 July 2006 06:47 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Why don't you do like this?

SELECT ID
     , col_1
  FROM (SELECT ID
             , col_1
             , ROW_NUMBER () OVER (ORDER BY ID) rnum
             , COUNT (*) OVER () cnt
          FROM temp_middle)
 WHERE rnum = TRUNC (cnt / 2)


        ID COL_1
---------- ----------
         5 Value 5

Query Your Dream & Future at SoQooL
http://www.soqool.com
Re: Middle Most Record in a Table [message #184104 is a reply to message #184081] Tue, 25 July 2006 06:57 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member


Thank you, so nice of you.

But I want to know,which language is there at the site you
provided. Is there anyway to get it in English..
Regards,

Anil
Re: Middle Most Record in a Table [message #184109 is a reply to message #184102] Tue, 25 July 2006 07:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

Why don't you do like this?


Mostly because I thought of my way first. Cool
If I'd looked a little clolser, I'd have noticed that I didn't need the Order By on the inner SELECT.

I'm suprised there isn't an easy way to find the Median though.

Here's 1.5 more ways to do it

SQL> select distinct
  2         max(id)           over (partition by half order by id desc)  id
  3        ,first_value(col_1) over (partition by half order by id desc)  col_1
  4  from (select id
  5              ,col_1
  6              ,ntile(2) over (order by id) half
  7        from temp_middle)
  8  where half = 1;

        ID COL_1
---------- ----------
         5 Value 5
SQL> select max(id)
  2        ,max(col_1) keep (dense_rank last order by id)  col_1
  3  from (select id
  4              ,col_1
  5              ,ntile(2) over (order by id) half
  6        from temp_middle)
  7  where half = 1;

   MAX(ID) COL_1
---------- ----------
         5 Value 5
Re: Middle Most Record in a Table [message #184111 is a reply to message #184081] Tue, 25 July 2006 07:35 Go to previous message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member


Great !!!

Anil
Previous Topic: Primary Key with a Condition (Constraints)
Next Topic: Calculation using Discoverer
Goto Forum:
  


Current Time: Wed Dec 07 12:28:46 CST 2016

Total time taken to generate the page: 0.08420 seconds