CODEWITHSUNDEEP
sqloracle-database
Michiel van Oosterhout
Michiel van Oosterhout

Reputation: 23084

Select rows up to and including the last row with the first value in a column

Given a result set like this, ordered by AccountId:

| AccountId | Foo | Bar | ... |
| 7981      | x   |  12 | ... |
| 7981      | y   |  23 | ... |
| 7981      | z   |   8 | ... |
| 7981      | q   | 142 | ... |
| 64734     | x   |  31 | ... |
| 64734     | q   |  12 | ... |

I want to select all rows with all columns for the first AccountId, regardless of the actual value of AccountId and regardless of the number of rows. (In other words, select all rows until the value in the first column changes.)

To get this result set takes a lot of joins and clauses, so using something like group by and a limit to get the first AccountId in a sub query is not the type of solution I am looking for. Because I will have to duplicate a lot of code.

I have been experimenting with ... over (partition by AccountId) functions. But they all seem to operate on the values inside the partitions created by the partition by.

One solution I envision is a function that assigns the number 1 to all rows in the first partition by AccountId partition, and the number 2 to all rows in the second partition, etc. Then I can just add where group = 1.

Upvotes: 0

Views: 79

Answers (5)

mohabbati
mohabbati

Reputation: 1158

The following query return the first AccountId with all other row data for that account. The query below is more optimized than other solution.

Update: I used multiple CTE to solve the problem. In the update, your query used only once.

with
    [query_result]
as
(
    select
        AccountId   ,
        Foo         ,
        Bar
    from
        [Your_Table]
),
    first_account
as
(
    select
        AccountId,
        row_number() over(order by AccountId) as RowNum
    from
        [query_result]
)
select
    r.AccountId ,
    r.Foo       ,
    r.Bar
from
    [query_result]  as r
inner join
    first_account   as fa
on
    r.AccountId = fa.AccountId
where
    fa.RowNum = 1;

Upvotes: 1

schurik
schurik

Reputation: 7928

one way to avoid the duplicate code is to use the WITH Clause

with complex_query AS
(
  SELECT AccountId, foo, bar, ...
  ... complex stuff here
)
SELECT *
FROM complex_query 
WHERE 
  AccountId = ( SELECT MIN(AccountId) FROM complex_query) 
;

If you prefer the analytic functions, you kann use them without specifying the Partition part, like this 

SELECT  MIN(AccountId) OVER() AS minAccountId, AccountId ...

then they operate on the entire result set

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

I'm not sure i'm understand you case. You can with analytic function.

 select * from (   
   select first_value(AccountId) over() my_first_AccountId, complex_query.*
    from 
    ( select AccountId ,... from t1,t2..,tn order by t1.x,t2.y,t3.z>  ) complex_query
     )
  where my_first_AccountId = AccountId

Working example 

 select * from (

 select first_value(table_name) over() my_first_value, complex_query.*
  from 
   ( select * from user_tab_cols order by table_name ) complex_query
  )
  where my_first_value = table_name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you only want to reference the table once, you can use window functions:

select t.*
from (select t.*, min(accountid) over () as min_accountid
      from t
     ) t
where min_accountid = accountid;

or rank():

select t.*
from (select t.*, row_number() over (order by accountid) as seqnum
      from t
     ) t
where seqnum = 1;

This is helpful if t is a complex query or view.

Upvotes: 1

Shuumi
Shuumi

Reputation: 193

How about selecting the minimum AccountId in a subquery and then showing all rows with this AccountId?

The code would be something like:

SELECT * 
FROM accounts
WHERE AccountId IN (
  SELECT MIN(AccountId)
  FROM accounts
)

EDIT: Since you said it is a quite long query, then you could make it a little faster by using EXISTS instead of IN operator:

SELECT * 
FROM accounts accsMain
WHERE EXISTS(
  SELECT MIN(AccountId)
  FROM accounts accsSub
  WHERE accsMain.AccountId = accsSub.AccountId
)

Upvotes: 1

Related Questions