SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select from list with partitioning / row order
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  08:30:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/23/2013 :  08:50:04  Show Profile  Reply with Quote
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


--
Chandu
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  09:57:09  Show Profile  Reply with Quote
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?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/23/2013 :  13:45:36  Show Profile  Reply with Quote
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?
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  15:28:26  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/23/2013 :  15:35:54  Show Profile  Reply with Quote
SELECT
	number, PurchaseID, [date],tranid,TraHistInD
FROM
(
	SELECT
		*,
		COUNT(*) OVER (PARTITION BY PurchaseID) AS N
	FROM
		@pur
) s WHERE N > 1;
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  15:42:20  Show Profile  Reply with Quote
excellent

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

cheers mate.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  16:14:20  Show Profile  Reply with Quote
are we aloud to use an order by with this method.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  17:49:55  Show Profile  Reply with Quote
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)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/23/2013 :  17:50:31  Show Profile  Reply with Quote
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;

Edited by - James K on 01/23/2013 17:50:46
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  18:00:00  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/23/2013 :  18:20:24  Show Profile  Reply with Quote
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?

Edited by - James K on 01/23/2013 18:22:03
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/23/2013 :  18:53:12  Show Profile  Reply with Quote
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?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3332 Posts

Posted - 01/23/2013 :  19:12:09  Show Profile  Reply with Quote
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?
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/24/2013 :  01:52:11  Show Profile  Reply with Quote
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.

Edited by - masterdineen on 01/24/2013 01:53:38
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/24/2013 :  02:04:24  Show Profile  Reply with Quote
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
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 01/24/2013 :  03:24:50  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 01/24/2013 :  03:58:30  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000