August 10, 2015 at 11:59 am
I have a 2 coloums that has to records
createdon Kms_QuoteNumber
2015-07-22 20:26:41.000 15-35553/Rev9
2015-07-22 21:32:31.000 15-35553/Rev10
where I run this it only shows me the 1st one. the /Rev10 is the one I
SELECT max(kms_QuoteNumber )
FROM quote
WHERE
[kms_quotenumber] LIKE '15-35553%'
August 10, 2015 at 12:05 pm
lcarrethers (8/10/2015)
I have a 2 coloums that has to recordscreatedon Kms_QuoteNumber
2015-07-22 20:26:41.000 15-35553/Rev9
2015-07-22 21:32:31.000 15-35553/Rev10
where I run this it only shows me the 1st one. the /Rev10 is the one I
SELECT max(kms_QuoteNumber )
FROM quote
WHERE
[kms_quotenumber] LIKE '15-35553%'
You're getting the correct results for your query.
Compare the same number of characters in each value. 15-35553/Rev9 is greater than 15-35553/Rev1
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 12:05 pm
can you post the CREATE TABLE and INSERT scripts so we can reproduce the problem?
August 10, 2015 at 12:08 pm
What about using a different approach?
This might be what you're looking for.
SELECT TOP (1) kms_QuoteNumber
FROM quote
WHERE [kms_quotenumber] LIKE '15-35553%'
ORDER BY createdon DESC
August 10, 2015 at 12:08 pm
pietlinden (8/10/2015)
can you post the CREATE TABLE and INSERT scripts so we can reproduce the problem?
You don't need those!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 12:13 pm
That works with only '15-35553%
that was just an example of one type of quote number. I want the make of any quote number added.
so when I took the where clause out it only gave me one.
15-35657
August 10, 2015 at 12:13 pm
Alvin Ramard (8/10/2015)
lcarrethers (8/10/2015)
I have a 2 coloums that has to recordscreatedon Kms_QuoteNumber
2015-07-22 20:26:41.000 15-35553/Rev9
2015-07-22 21:32:31.000 15-35553/Rev10
where I run this it only shows me the 1st one. the /Rev10 is the one I
SELECT max(kms_QuoteNumber )
FROM quote
WHERE
[kms_quotenumber] LIKE '15-35553%'
You're getting the correct results for your query.
Compare the same number of characters in each value. 15-35553/Rev9 is greater than 15-35553/Rev1
You need to isolate the number(s) that come after /REV and compare those as number, so 9 gets compared against 10. The way you're doing it, the character 9 gets compared against the character 1. 9 is greater than 1.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 12:32 pm
Considering the following options. Which one is right?
CREATE TABLE #SampleData(
createdon datetime,
Kms_QuoteNumber varchar( 100))
INSERT INTO #SampleData
VALUES('2015-07-22 20:26:41.000', '15-35553/Rev9'),
('2015-07-22 21:32:31.000', '15-35553/Rev10'),
('2015-07-22 20:26:41.000', '15-37779/Rev8'),
('2015-07-22 21:32:31.000', '15-37779/Rev7');
WITH cteRowNums AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY LEFT( Kms_QuoteNumber, CHARINDEX( '/', Kms_QuoteNumber)) ORDER BY createdon DESC) rownum
FROM #SampleData
)
SELECT *
FROM cteRowNums
WHERE rownum = 1;
WITH cteRowNums AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY LEFT( Kms_QuoteNumber, CHARINDEX( '/', Kms_QuoteNumber))
ORDER BY CAST( SUBSTRING( Kms_QuoteNumber, CHARINDEX( '/Rev', Kms_QuoteNumber) + 4, 10) AS int) DESC) rownum
FROM #SampleData
)
SELECT *
FROM cteRowNums
WHERE rownum = 1;
DROP TABLE #SampleData;
August 10, 2015 at 1:41 pm
CREATE TABLE test
(
createdon datetime,
kms_quoteorder varchar(15)
)
INSERT INTO test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')
INSERT INTO test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')
INSERT INTO test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')
INSERT INTO test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')
select * from test
drop table test
So all that I want to see is the max one for each kms_quoteorder
August 10, 2015 at 1:42 pm
lcarrethers (8/10/2015)
So all that I want to see is the max one for each kms_quoteorder
How do you define the max one?
August 10, 2015 at 2:13 pm
lcarrethers (8/10/2015)
CREATE TABLE test(
createdon datetime,
kms_quoteorder varchar(15)
)
INSERT INTO test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')
INSERT INTO test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')
INSERT INTO test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')
INSERT INTO test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')
select * from test
So all that I want to see is the max one for each kms_quoteorder
Base on the above:
with base as (
select
*,
rn = row_number() over (partition by left(kms_quoteorder,charindex('/',kms_quoteorder) - 1) order by cast(right(kms_quoteorder,len(kms_quoteorder) - charindex('/',kms_quoteorder) - 3) as int) desc)
from
test
)
select createdon, kms_quoteorder
from base
where rn = 1;
go
August 10, 2015 at 2:25 pm
I get this error
Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
August 10, 2015 at 2:32 pm
That suggests you have kms_quoteorder values that do not contain a '/'. In that case, charindex would return 0, and after subtracting the 1 would give -1, which is indeed invalid.
It's difficult to suggest a full solution, since apparently we don't have fully representative sample data. If you could provide sample data that shows all the patterns kms_quoteorder uses, that would help a lot.
Cheers!
August 10, 2015 at 2:38 pm
lcarrethers (8/10/2015)
I get this errorMsg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
As I stated, it worked for what you provided. Need more info if the sample data you provided is not 100% representative of your existing data.
August 10, 2015 at 2:38 pm
Sorry about that. The values are for kms_quotenumber are
EX.
NULL
12-00013
13-01646/Rev1
13-01646/Rev10
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply