Partition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Partition

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2009-02-11 : 14:25:05

Hello,

I'm trying to pull the top 2 rows per account_id from a table as a subquery. This query is returning only one of each account_id.

Does anyone see where its going wrong?

WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
)
-- and select the data from the CTE
SELECT
account_id,
current_premium,
datecreated,
past_due_amount
FROM dt
WHERE RowNumber <= 2

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 14:28:53

quote:
Originally posted by oahu9872

Hello,

I'm trying to pull the top 2 rows per account_id from a table as a subquery. This query is returning only one of each account_id.

Does anyone see where its going wrong?

WITH dt AS (
SELECT
DENSE_RANK() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
)
-- and select the data from the CTE
SELECT
account_id,
current_premium,
datecreated,
past_due_amount
FROM dt
WHERE RowNumber <= 2

oahu9872
Posting Yak Master

112 Posts

Posted - 2009-02-11 : 14:33:20

It is still just pulling one row per account ID

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-11 : 14:41:39

quote:
Originally posted by oahu9872

It is still just pulling one row per account ID

Then you don't have it.

oahu9872
Posting Yak Master

112 Posts

Posted - 2009-02-11 : 15:24:34

I've pulled specific account_id's out and found between 1 and 20 records each, so it should return 2 records for each. the data is there.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 03:26:39

quote:
Originally posted by oahu9872

Hello,
I'm trying to pull the top 2 rows per account_id from a table as a subquery. This query is returning only one of each account_id.

Does anyone see where its going wrong?
WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
)
-- and select the data from the CTE
SELECT
account_id,
current_premium,
datecreated,
past_due_amount
FROM dt
WHERE RowNumber <= 2

actually why r using cte for this there is no hierarcal data in this
use derived tables try this once
select account_id,
current_premium,
past_due_amount,
dateCreated
from (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
) where rownumber < 3

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-12 : 03:34:19

quote:
Originally posted by bklr

quote:
Originally posted by oahu9872

Hello,
I'm trying to pull the top 2 rows per account_id from a table as a subquery. This query is returning only one of each account_id.

Does anyone see where its going wrong?
WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
)
-- and select the data from the CTE
SELECT
account_id,
current_premium,
datecreated,
past_due_amount
FROM dt
WHERE RowNumber <= 2

select t.account_id,
t.current_premium,
t.past_due_amount,
t.dateCreated
from (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
) t where t.rownumber < 3

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 03:46:18

quote:
Originally posted by oahu9872

I've pulled specific account_id's out and found between 1 and 20 records each, so it should return 2 records for each. the data is there.

1 record cannot turn into two by itself.
We are not dealing with magic.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:45:08

quote:
Originally posted by oahu9872

Hello,

I'm trying to pull the top 2 rows per account_id from a table as a subquery. This query is returning only one of each account_id.

Does anyone see where its going wrong?

WITH dt AS (
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS 'RowNumber',
account_id,
current_premium,
past_due_amount,
dateCreated
FROM chargeable_invoice
)
-- and select the data from the CTE
SELECT
account_id,
current_premium,
datecreated,
past_due_amount
FROM dt
WHERE RowNumber <= 2

the query is correct. as suggested, you dont seem to contain 2 records per account id. try this and see what it returns


select account_id,count(*)
from chargeable_invoice
group by account_id
having count(*)>1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:00:11

quote:

Originally posted by visakh16

select account_id,count(*)
from chargeable_invoice
group by account_id
having count(*)>1

This will not present the accounts with onyl one record

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:05:47

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

select account_id,count(*)
from chargeable_invoice
group by account_id
having count(*)>1
This will not present the accounts with onyl one record

E 12°55'05.63"
N 56°04'39.26"

the query i gave was to identify if there are any groups of records with same value of account_id

oahu9872
Posting Yak Master

112 Posts

Posted - 2009-02-12 : 10:28:32

Here is a sample from the results (there are thousands of account_id values). each one is in the table multiple times, however all of the queries are returning each account id only 1 time.

account_id
3729 6
3741 12
3757 10
3760 5
3773 19
3780 11
3785 7
3789 4
3797 3
3818 2
3823 6
3826 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:31:27

quote:
Originally posted by oahu9872

Here is a sample from the results (there are thousands of account_id values). each one is in the table multiple times, however all of the queries are returning each account id only 1 time.

account_id
3729 6
3741 12
3757 10
3760 5
3773 19
3780 11
3785 7
3789 4
3797 3
3818 2
3823 6
3826 5

ok so you've account_id's occuring more than once. what does below return then?


SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS Seq,
account_id,
current_premium,
past_due_amount, 
dateCreated
FROM chargeable_invoice
)t
WHERE t.Seq>1

SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY account_id ORDER BY datecreated DESC ) AS Seq,
account_id,
current_premium,
past_due_amount, 
dateCreated
FROM chargeable_invoice
)t
WHERE t.Seq=1

oahu9872
Posting Yak Master

112 Posts

Posted - 2009-02-12 : 11:14:17

Each query returns several thousand results. I have about a half million account_id's and most have more than one occurrence and several only have one. But in your first query I could see the account_id's multiple times. Is there an easy way to limit it to 2?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 11:18:31

quote:
Originally posted by oahu9872

Each query returns several thousand results. I have about a half million account_id's and most have more than one occurrence and several only have one. But in your first query I could see the account_id's multiple times. Is there an easy way to limit it to 2?

Where t.seq =2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 11:23:02

Where t.seq <=2

if you want 2 per account_id

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-12 : 23:08:15

hi oahu9872
i have given the query, i think it will return the 2 records per account_id once check by using it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources