First and Last number

Question

Post reply

First and Last number

kapil_kk

SSC-Insane

Points: 21316
More actions
March 18, 2013 at 5:14 am

#275301

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/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

SELECT TransactionID, MIN(VoucherNo) , MAX(VoucherNo )

FROM yourtable

GROUP BY TransactionID

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

;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/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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:

http://msdn.microsoft.com/en-us/library/ms187751.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

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:
http://msdn.microsoft.com/en-us/library/ms187751.aspx

Thanks Eugene 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

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/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

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.";-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]