March 18, 2013 at 5:14 am
I have table in which i have data like - Columns are VoucherNo and TransactionID
PD0000021TRN0000003
PD0000022TRN0000003
PD0000023TRN0000003
PD0000024TRN0000003
PD0000025TRN0000003
PD0000026TRN0000003
PD0000027TRN0000003
PD0000028TRN0000003
PD0000029TRN0000003
PD0000030TRN0000003
VB0000001TRN0000001
VB0000002TRN0000001
VB0000003TRN0000001
VB0000004TRN0000001
VB0000005TRN0000001
VB0000006TRN0000001
VB0000007TRN0000001
VB0000008TRN0000001
VB0000009TRN0000001
VB0000010TRN0000001
Now here there are no of vouchersno for a particular TransactionID. I want first and last voucher for every transactionID e.g. For TRN0000003 i want PD0000021 and PD0000030
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2013 at 5:42 am
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
March 18, 2013 at 5:48 am
;with cte as
((select min(VoucherNo) VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo asc
)
union all
select max(VoucherNo)VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo desc
)
select * from cte
Thsi will also works
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2013 at 5:49 am
Eugene Elutin (3/18/2013)
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
can you plz explain me how Min, MAX is working with alphanumeric values like 'VB00001'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2013 at 5:58 am
kapil_kk (3/18/2013)
;with cte as((select min(VoucherNo) VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo asc
)
union all
select max(VoucherNo)VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo desc
)
select * from cte
Thsi will also works
Yes, but it will take about twice as long to execute as Eugene's solution. Why process the same rows twice when a simpler query processes them just once?
Gratuitously increasing the complexity of a query is a dangerous business. Other folks looking at your query will waste time working out what it's trying to do, because you haven't written it in the simplest - and most obvious - way.
Also, Eugene's solution provides the results in one row...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2013 at 6:16 am
kapil_kk (3/18/2013)
Eugene Elutin (3/18/2013)
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
can you plz explain me how Min, MAX is working with alphanumeric values like 'VB00001'
In the same way as with numeric values.
MIN and MAX wouldn't work for BIT, but other datatypes are absolutely fine:
March 18, 2013 at 6:19 am
Eugene Elutin (3/18/2013)
kapil_kk (3/18/2013)
Eugene Elutin (3/18/2013)
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
can you plz explain me how Min, MAX is working with alphanumeric values like 'VB00001'
In the same way as with numeric values.
MIN and MAX wouldn't work for BIT, but other datatypes are absolutely fine:
Thanks Eugene
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2013 at 7:18 am
ChrisM@Work (3/18/2013)
kapil_kk (3/18/2013)
;with cte as((select min(VoucherNo) VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo asc
)
union all
select max(VoucherNo)VoucherNo, TransactionID from GV_Voucher
group by TransactionID
--order by VoucherNo desc
)
select * from cte
Thsi will also works
Yes, but it will take about twice as long to execute as Eugene's solution. Why process the same rows twice when a simpler query processes them just once?
Gratuitously increasing the complexity of a query is a dangerous business. Other folks looking at your query will waste time working out what it's trying to do, because you haven't written it in the simplest - and most obvious - way.
Also, Eugene's solution provides the results in one row...
Chris I write that query because I thought Max, MIN operation will not work with alphanumeric values.
I was so lazy that at least I didn't give a try with MIN and MAX :-D:hehe:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 19, 2013 at 7:44 am
Eugene Elutin (3/18/2013)
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
Just a small point, but this only works as long as the values are fixed width and zero padded. If that is the case you are good to go. If not, you will need a better mousetrap.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2013 at 7:47 am
TheSQLGuru (3/19/2013)
Eugene Elutin (3/18/2013)
SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )
FROM yourtable
GROUP BY TransactionID
Just a small point, but this only works as long as the values are fixed width and zero padded. If that is the case you are good to go. If not, you will need a better mousetrap.
I would clarify it a bit:
This always works! But...
It produces human-logical results "as long as the values are fixed width and zero padded.";-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy