I have a MySQL table and want to start retrieving from the middle of the table, but I don’t have a unique ID and don’t want to retrieve all of the columns. When I start retrieving the query will start from the first row.
Colum A | Colum B | Colum C |
---|---|---|
12364 | CC | 4785457 |
88364 | AR | 6874688 |
12774 | BB | 6847854 |
19864 | VV | 4768558 |
17482 | MM | 5835738 |
27549 | TT | 5764866 |
I want to start retrieving from the third row, the columns to retrieve are B and C.
8
As already commented, it couldn’t meet the expectations (whatever expected it is) without an order by of some kind. You just couldn’t know what the resultset could be.
Here is a sample without Order By and two samples with different Order By…
WITH -- S a m p l e D a t a :
tbl (col_a, col_b, col_c) AS
( Select 12364, 'CC', 4785457 Union All
Select 88364, 'AR', 6874688 Union All
Select 12774, 'BB', 6847854 Union All
Select 19864, 'VV', 4768558 Union All
Select 17482, 'MM', 5835738 Union All
Select 27549, 'TT', 5764866
)
Without a proper Order By clause in Row_Number() Over() analytic function your resultset could be different every time you run the query.
Sample 1 – no Order By clause – in Row_number() Over() analytic function – ( result is unpredictable )
SELECT t.col_b, t.col_c
FROM ( Select Row_Number() Over() as rn, Count(*) Over() as cnt, col_b, col_c
From tbl ) t
WHERE t.rn >= Floor( t.cnt / 2 )
ORDER BY rn
/* R e s u l t :
col_b col_c
----- --------
BB 6847854
VV 4768558
MM 5835738
TT 5764866 */
With Order By clause in Row_Number() Over() analytic function your resultset is more convenient.
Sample 2 – Order By clause – Row_number() Over(Order By col_b) analytic function
SELECT t.col_b, t.col_c
FROM ( Select Row_Number() Over(Order By col_b) as rn, Count(*) Over() as cnt, col_b, col_c
From tbl ) t
WHERE t.rn >= Floor( t.cnt / 2 )
ORDER BY rn
/* R e s u l t :
col_b col_c
----- --------
CC 4785457
MM 5835738
TT 5764866
VV 4768558 */
With Order By another column values in Descending order …
Sample 3 – Order By clause – Row_number() Over(Order By col_c Desc) analytic function
SELECT t.col_b, t.col_c
FROM ( Select Row_Number() Over(Order By col_c Desc) as rn, Count(*) Over() as cnt, col_b, col_c
From tbl ) t
WHERE t.rn >= Floor( t.cnt / 2 )
ORDER BY rn
/* R e s u l t :
col_b col_c
----- --------
MM 5835738
TT 5764866
CC 4785457
VV 4768558 */
See the fiddle here.