Select from list with partitioning / row order

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 2008 Forums

Transact-SQL (2008)

Select from list with partitioning / row order

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 08:30:06

Hello there.

see example data below.

number PurchaseID date tranid TraHistInD
1 57374 2001-06-11 17:17:47.483 2676209 244893
2 57374 2001-06-12 09:22:57.623 2676209 244897
3 57374 2001-06-12 09:23:41.517 2676209 244898
4 29436 2001-06-12 09:30:54.483 2676209 244900

at the moment i have the below partioning script.

(select *, row_number() over (partition by transactionid
order by transactionpurchasedate asc, transactionhistoryid desc ) as number from(...

BUT. What i need to acheive is. Select the highest TranHistID
but then if we have more than one Purchase id listed like above.
I need to have the earliest date first.

so the above would look like. (see below)

number PurchaseID date tranid TraHistID
1 29436 2001-06-12 09:30:54.483 2676209 244900
2 57374 2001-06-11 17:17:47.483 2676209 244893
3 57374 2001-06-12 09:22:57.623 2676209 244897
4 57374 2001-06-12 09:23:41.517 2676209 244898

if tranHistId 244900 was not in the list. we would have

number PurchaseID date tranid TraHistID
1 57374 2001-06-11 17:17:47.483 2676209 244893
2 57374 2001-06-12 09:22:57.623 2676209 244897
3 57374 2001-06-12 09:23:41.517 2676209 244898

How can i achieve two sorts like that.

Regared

Rob

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-23 : 08:50:04

[code]DECLARE @pur TABLE(number int, PurchaseID int, date datetime, tranid int, TraHistInD int)
insert into @pur
SELECT 1, 57374, '2001-06-11 17:17:47.483', 2676209, 244893union all
SELECT 2, 57374, '2001-06-12 09:22:57.623', 2676209, 244897 union all
SELECT 3, 57374, '2001-06-12 09:23:41.517', 2676209, 244898 union all
SELECT 4, 29436, '2001-06-12 09:30:54.483', 2676209, 244900

select *, row_number() over (partition by PurchaseID
order by date asc,TraHistInD desc) as number
from @pur[/code]

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 09:57:09

thank you for that.

no my result is

number PurchaseID date tranid TraHistInD sort
4 29436 2001-06-12 09:30:54.483 2676209 244900 1
1 57374 2001-06-11 17:17:47.483 2676209 244893 1
2 57374 2001-06-12 09:22:57.623 2676209 244897 2
3 57374 2001-06-12 09:23:41.517 2676209 244898 3

how can i select the first 1 value row from the sort column?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 13:45:36

If you just want one row, use TOP (1).

select TOP(1) *, row_number() over (partition by PurchaseID
order by date asc,TraHistInD desc) as number 
from @pur

If you have multiple groups, and you want the results to show the first 1 from each group, you would need to use something like row_number or rank functions. Is that what you need, or is it just one row you are looking for?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 15:28:26

with the example below.

number PurchaseID date tranid TraHistID
1 29436 2001-06-12 09:30:54.483 2676209 244900
2 57374 2001-06-11 17:17:47.483 2676209 244893
3 57374 2001-06-12 09:22:57.623 2676209 244897
4 57374 2001-06-12 09:23:41.517 2676209 244898

how do i select only the records that have more than one purchaseid and tranid ie records 2 - 4

and leave out the record where there is only one purchaseid ie record 1

the above is just a example i need to do this to a big dataset. so there will lots of the above.

Regards

Rob

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 15:35:54

[code]SELECT
number, PurchaseID, [date],tranid,TraHistInD
FROM
(
SELECT
*,
COUNT(*) OVER (PARTITION BY PurchaseID) AS N
FROM
@pur
) s WHERE N > 1;[/code]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 15:42:20

excellent

thank you, now i can do two runs to get what i need,

cheers mate.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 16:14:20

are we aloud to use an order by with this method.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 17:49:55

quote:

Originally posted by James K

SELECT
	number, PurchaseID, [date],tranid,TraHistInD
FROM
(
	SELECT
		*,
		COUNT(*) OVER (PARTITION BY PurchaseID) AS N
	FROM
		@pur
) s WHERE N > 1;

Could i order my count(*) by a column ie

COUNT(*) OVER (PARTITION BY PurchaseID order by trandate)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 17:50:31

quote:
Originally posted by masterdineen

are we aloud to use an order by with this method.

Yes, you should be able to use order by any of the columns in the select list.

SELECT
	number, PurchaseID, [date],tranid,TraHistInD
FROM
(
	SELECT
		*,
		COUNT(*) OVER (PARTITION BY PurchaseID) AS N
	FROM
		@pur
) s WHERE N > 1
order by PurchaseID,number;

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 18:00:00

what about using the order by within

COUNT(*) OVER (PARTITION BY PurchaseID) AS N

just like the example to begin with.

select *, row_number() over (partition by PurchaseID
order by date asc,TraHistInD desc) as number
from @pur

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 18:20:24

COUNT(*) does not let you use order by in SQL 2008 windowing functions. The implementation of windowing functions in SQL 2008 is rather limited - it does not let you specify any frame boundaries. Given that, when you compute the COUNT, the ordering does not mean anything - you are simply counting how many rows there are in the frame.

Just out of curiosity, what is the reason you are attempting to use ORDER BY in the OVER clause?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-23 : 18:53:12

i will try and explain the best i can. see example data below

number PurchaseID date tranid TraHistID
1 29436 2001-06-12 09:30:54.483 2676209 244900
2 57374 2001-06-11 17:17:47.483 2676209 244893
3 57374 2001-06-12 09:22:57.623 2676209 244897
4 57374 2001-06-12 09:23:41.517 2676209 244898

i have a list of transactions. and i am looking for tranID's with the highest tranHist. If there are tranid's with more than one
purchaseID, then i want to take the earliest date. So i was thinking of doing this in two stages. selecting with an order by Count where (having count(purchaseID =1 into a temp table. Then do the same with count(purchaseID) >1 into the same temp table.

so basically. search for transactionIDs with the highest HistTran. But if transactionID has Count(purchaseID) >1 then take the earliest entry date.

does that make sense?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-23 : 19:12:09

I did not quite follow the logic. For the sample data that you posted with the four rows, can you describe the sequence of steps you would go through to get the result you are looking for?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 01:52:11

number PurchaseID date tranid TraHistID
1 29436 2001-06-12 09:30:54.483 2676209 244900
2 57374 2001-06-11 17:17:47.483 2676209 244893
3 57374 2001-06-12 09:22:57.623 2676209 244897
4 57374 2001-06-12 09:23:41.517 2676209 244898

my first routine. would be I'm looking for all trainIDs that only have one
tranHistId per purchaseID. like the first row above

second routine. I am looking for all tranIDs that have more than one
TranHistIDs per purchaseID. then order that by the tranHistDate with
the earliest first. like the last three above.

is that better.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 02:04:24

quote:
Originally posted by masterdineen

i have a list of transactions. and i am looking for tranID's with the highest tranHist. If there are tranid's with more than one
purchaseID, then i want to take the earliest date. So i was thinking of doing this in two stages. selecting with an order by Count where (having count(purchaseID =1 into a temp table. Then do the same with count(purchaseID) >1 into the same temp table.

so basically. search for transactionIDs with the highest HistTran. But if transactionID has Count(purchaseID) >1 then take the earliest entry date.
does that make sense?

May be this.........?


SELECT number, PurchaseID, date, tranid, TraHistInD
FROM (select *, row_number() over (partition by PurchaseID 	order by date asc,TraHistInD desc) as rn 
		FROM @pur
	  ) t
WHERE t.rn = 1;

If this is not, can you post the expected output for either cases
(i.e.
  1--> search for transactionIDs with the highest HistTran. 
  2--> But if transactionID has Count(purchaseID) >1 then take the earliest entry date.
) ?

--
Chandu

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2013-01-24 : 03:24:50

This works with the little example data in this post, but i have added an extra column to partition on.

and that seems to work at the moment. Thank you. Now im going to see if i have all the latest transactions without the history.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-24 : 03:58:30

quote:
Originally posted by masterdineen

This works with the little example data in this post, but i have added an extra column to partition on.

and that seems to work at the moment. Thank you. Now im going to see if i have all the latest transactions without the history.

Ok welcome... Revert us back in the case of any issue

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources