|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with counting QueryI have files which are suffixed with -R and then a number from 1 to 3 and then two digits 00-14. the field is char i.e.: -R101 -R102 -R114 -R201 -R302 I need to determine how many 101, how many 102, etc through 114 and then the same for the 201-214 series and then the 301-314 series. I will be sending the query from Visual Basic using ADODC so I am not sure how the data will be returned. If it was written to a temp table that would be great. Thanks, Bob Hiller Lifts for the Disabled LLC select right(columnName, 3), count(columnName)
from tableName group by right(columnName, 3) Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >I am trying to find a query to count rows with multiple conditions. > > I have files which are suffixed with -R and then a number from 1 to 3 and > then two digits 00-14. > the field is char > > i.e.: > -R101 > -R102 > -R114 > -R201 > -R302 > > I need to determine how many 101, how many 102, etc through 114 and then > the same for > the 201-214 series and then > the 301-314 series. > > I will be sending the query from Visual Basic using ADODC so I am not sure > how the data will be returned. If it was written to a temp table that > would be great. > > Thanks, > Bob Hiller > Lifts for the Disabled LLC > > > > Thank you,
That worked great but I did not ask the full question. Maybe you can help again. if these strings are in a column 12345678-R101 12345678-R201 12345678-R301 98564512-R112 18752381-R101 18752381-R201 18752381-R110 18752381-R111 18752381-R211 If there is a -R2 there will always be a -R1. Likewise if there is a -R3 there will always be a -R2. In the above example I need to return 12345678-R301 98564512-R112 18752381-R201 18752381-R110 18752381-R211 I hope I have explained this well enough. Thanks, Bob Hiller Lifts for the Disabled LLC Show quote "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... > select right(columnName, 3), count(columnName) > from tableName > group by right(columnName, 3) > > "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message > news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>I am trying to find a query to count rows with multiple conditions. >> >> I have files which are suffixed with -R and then a number from 1 to 3 and >> then two digits 00-14. >> the field is char >> >> i.e.: >> -R101 >> -R102 >> -R114 >> -R201 >> -R302 >> >> I need to determine how many 101, how many 102, etc through 114 and then >> the same for >> the 201-214 series and then >> the 301-314 series. >> >> I will be sending the query from Visual Basic using ADODC so I am not >> sure how the data will be returned. If it was written to a temp table >> that would be great. >> >> Thanks, >> Bob Hiller >> Lifts for the Disabled LLC >> >> >> >> > > select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10)) from tableName group by left(columnName, charindex('-R',columnName)+1) Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... > Thank you, > That worked great but I did not ask the full question. Maybe you can help > again. > > if these strings are in a column > 12345678-R101 > 12345678-R201 > 12345678-R301 > > 98564512-R112 > > 18752381-R101 > 18752381-R201 > > 18752381-R110 > > 18752381-R111 > 18752381-R211 > > If there is a -R2 there will always be a -R1. Likewise if there is a -R3 > there will always be a -R2. > In the above example I need to return > 12345678-R301 > > 98564512-R112 > > 18752381-R201 > > 18752381-R110 > > 18752381-R211 > > I hope I have explained this well enough. > > Thanks, > Bob Hiller > Lifts for the Disabled LLC > > > "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message > news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >> select right(columnName, 3), count(columnName) >> from tableName >> group by right(columnName, 3) >> >> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>I am trying to find a query to count rows with multiple conditions. >>> >>> I have files which are suffixed with -R and then a number from 1 to 3 >>> and then two digits 00-14. >>> the field is char >>> >>> i.e.: >>> -R101 >>> -R102 >>> -R114 >>> -R201 >>> -R302 >>> >>> I need to determine how many 101, how many 102, etc through 114 and then >>> the same for >>> the 201-214 series and then >>> the 301-314 series. >>> >>> I will be sending the query from Visual Basic using ADODC so I am not >>> sure how the data will be returned. If it was written to a temp table >>> that would be great. >>> >>> Thanks, >>> Bob Hiller >>> Lifts for the Disabled LLC >>> >>> >>> >>> >> >> > > OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1), MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) from tableName group by left(columnName, charindex('-R',columnName)+1) Show quote "helpful sql" <nospam@stopspam.com> wrote in message news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... > select left(columnName, charindex('-R',columnName)+1), > MAX(substring(columnName,charindex('-R',columnName)+2,10)) > from tableName > group by left(columnName, charindex('-R',columnName)+1) > > "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message > news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >> Thank you, >> That worked great but I did not ask the full question. Maybe you can help >> again. >> >> if these strings are in a column >> 12345678-R101 >> 12345678-R201 >> 12345678-R301 >> >> 98564512-R112 >> >> 18752381-R101 >> 18752381-R201 >> >> 18752381-R110 >> >> 18752381-R111 >> 18752381-R211 >> >> If there is a -R2 there will always be a -R1. Likewise if there is a -R3 >> there will always be a -R2. >> In the above example I need to return >> 12345678-R301 >> >> 98564512-R112 >> >> 18752381-R201 >> >> 18752381-R110 >> >> 18752381-R211 >> >> I hope I have explained this well enough. >> >> Thanks, >> Bob Hiller >> Lifts for the Disabled LLC >> >> >> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>> select right(columnName, 3), count(columnName) >>> from tableName >>> group by right(columnName, 3) >>> >>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>I am trying to find a query to count rows with multiple conditions. >>>> >>>> I have files which are suffixed with -R and then a number from 1 to 3 >>>> and then two digits 00-14. >>>> the field is char >>>> >>>> i.e.: >>>> -R101 >>>> -R102 >>>> -R114 >>>> -R201 >>>> -R302 >>>> >>>> I need to determine how many 101, how many 102, etc through 114 and >>>> then the same for >>>> the 201-214 series and then >>>> the 301-314 series. >>>> >>>> I will be sending the query from Visual Basic using ADODC so I am not >>>> sure how the data will be returned. If it was written to a temp table >>>> that would be great. >>>> >>>> Thanks, >>>> Bob Hiller >>>> Lifts for the Disabled LLC >>>> >>>> >>>> >>>> >>> >>> >> >> > > I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results. For one thing it is returning 2 expressions. I would expect only one. Thanks, Bob Hiller Lifts for the Disabled LLC Show quote "helpful sql" <nospam@stopspam.com> wrote in message news:OsGYae4TGHA.4792@TK2MSFTNGP14.phx.gbl... > OOPS... You need to convert the count values to int... > > select left(columnName, charindex('-R',columnName)+1), > MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) > from tableName > group by left(columnName, charindex('-R',columnName)+1) > > "helpful sql" <nospam@stopspam.com> wrote in message > news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... >> select left(columnName, charindex('-R',columnName)+1), >> MAX(substring(columnName,charindex('-R',columnName)+2,10)) >> from tableName >> group by left(columnName, charindex('-R',columnName)+1) >> >> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >> news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >>> Thank you, >>> That worked great but I did not ask the full question. Maybe you can >>> help again. >>> >>> if these strings are in a column >>> 12345678-R101 >>> 12345678-R201 >>> 12345678-R301 >>> >>> 98564512-R112 >>> >>> 18752381-R101 >>> 18752381-R201 >>> >>> 18752381-R110 >>> >>> 18752381-R111 >>> 18752381-R211 >>> >>> If there is a -R2 there will always be a -R1. Likewise if there is a -R3 >>> there will always be a -R2. >>> In the above example I need to return >>> 12345678-R301 >>> >>> 98564512-R112 >>> >>> 18752381-R201 >>> >>> 18752381-R110 >>> >>> 18752381-R211 >>> >>> I hope I have explained this well enough. >>> >>> Thanks, >>> Bob Hiller >>> Lifts for the Disabled LLC >>> >>> >>> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >>> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>>> select right(columnName, 3), count(columnName) >>>> from tableName >>>> group by right(columnName, 3) >>>> >>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>>I am trying to find a query to count rows with multiple conditions. >>>>> >>>>> I have files which are suffixed with -R and then a number from 1 to 3 >>>>> and then two digits 00-14. >>>>> the field is char >>>>> >>>>> i.e.: >>>>> -R101 >>>>> -R102 >>>>> -R114 >>>>> -R201 >>>>> -R302 >>>>> >>>>> I need to determine how many 101, how many 102, etc through 114 and >>>>> then the same for >>>>> the 201-214 series and then >>>>> the 301-314 series. >>>>> >>>>> I will be sending the query from Visual Basic using ADODC so I am not >>>>> sure how the data will be returned. If it was written to a temp table >>>>> that would be great. >>>>> >>>>> Thanks, >>>>> Bob Hiller >>>>> Lifts for the Disabled LLC >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample: 12345678-R301 98564512-R112 18752381-R211 I am missing: 18752381-R201 18752381-R110 Think of the first number after the -R as a counter for the last 2 numbers that represent a group. When these are in the table 18752381-R101 01 is the group and 1 is the counter 18752381-R201 01 is the group and 2 is the counter we want to return the largest counter for group 01 for the number to the left of -R return (18752381-R201) 18752381-R110 10 is the group and 1 is the counter return (18752381-R110) it is the only group 10 for the number to the left of -R 18752381-R111 11 is the group and 1 is the counter 18752381-R211 11 is the group and 2 is the counter return (18752381-R211) Thank in advance, Bob Hiller Lifts for the Disabled LLC Show quote "helpful sql" <nospam@stopspam.com> wrote in message news:OsGYae4TGHA.4792@TK2MSFTNGP14.phx.gbl... > OOPS... You need to convert the count values to int... > > select left(columnName, charindex('-R',columnName)+1), > MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) > from tableName > group by left(columnName, charindex('-R',columnName)+1) > > "helpful sql" <nospam@stopspam.com> wrote in message > news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... >> select left(columnName, charindex('-R',columnName)+1), >> MAX(substring(columnName,charindex('-R',columnName)+2,10)) >> from tableName >> group by left(columnName, charindex('-R',columnName)+1) >> >> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >> news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >>> Thank you, >>> That worked great but I did not ask the full question. Maybe you can >>> help again. >>> >>> if these strings are in a column >>> 12345678-R101 >>> 12345678-R201 >>> 12345678-R301 >>> >>> 98564512-R112 >>> >>> 18752381-R101 >>> 18752381-R201 >>> >>> 18752381-R110 >>> >>> 18752381-R111 >>> 18752381-R211 >>> >>> If there is a -R2 there will always be a -R1. Likewise if there is a -R3 >>> there will always be a -R2. >>> In the above example I need to return >>> 12345678-R301 >>> >>> 98564512-R112 >>> >>> 18752381-R201 >>> >>> 18752381-R110 >>> >>> 18752381-R211 >>> >>> I hope I have explained this well enough. >>> >>> Thanks, >>> Bob Hiller >>> Lifts for the Disabled LLC >>> >>> >>> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >>> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>>> select right(columnName, 3), count(columnName) >>>> from tableName >>>> group by right(columnName, 3) >>>> >>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>>I am trying to find a query to count rows with multiple conditions. >>>>> >>>>> I have files which are suffixed with -R and then a number from 1 to 3 >>>>> and then two digits 00-14. >>>>> the field is char >>>>> >>>>> i.e.: >>>>> -R101 >>>>> -R102 >>>>> -R114 >>>>> -R201 >>>>> -R302 >>>>> >>>>> I need to determine how many 101, how many 102, etc through 114 and >>>>> then the same for >>>>> the 201-214 series and then >>>>> the 301-314 series. >>>>> >>>>> I will be sending the query from Visual Basic using ADODC so I am not >>>>> sure how the data will be returned. If it was written to a temp table >>>>> that would be great. >>>>> >>>>> Thanks, >>>>> Bob Hiller >>>>> Lifts for the Disabled LLC >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > select max(a)
from(select '12345678-R101' union all select '12345678-R201' union all select '12345678-R301' union all select '98564512-R112' union all select '18752381-R101' union all select '18752381-R201' union all select '18752381-R110' union all select '18752381-R111' union all select '18752381-R211')x(a) group by left(a,8),right(a,2) -- Show quote-oj "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:Ow6lDS5TGHA.4600@TK2MSFTNGP11.phx.gbl... > Ok, I can live with the 2 returned expressions, they will work fine. > > Here is what is returned when I run the sample: > 12345678-R301 > 98564512-R112 > 18752381-R211 > > I am missing: > 18752381-R201 > 18752381-R110 > > Think of the first number after the -R as a counter for the last 2 numbers > that represent a group. > When these are in the table > > 18752381-R101 01 is the group and 1 is the counter > 18752381-R201 01 is the group and 2 is the counter > we want to return the largest counter for group 01 for the number to the > left of -R > return (18752381-R201) > > 18752381-R110 10 is the group and 1 is the counter > return (18752381-R110) it is the only group 10 for the number to the > left of -R > > 18752381-R111 11 is the group and 1 is the counter > 18752381-R211 11 is the group and 2 is the counter > return (18752381-R211) > > Thank in advance, > Bob Hiller > Lifts for the Disabled LLC > > > > "helpful sql" <nospam@stopspam.com> wrote in message > news:OsGYae4TGHA.4792@TK2MSFTNGP14.phx.gbl... >> OOPS... You need to convert the count values to int... >> >> select left(columnName, charindex('-R',columnName)+1), >> MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) >> from tableName >> group by left(columnName, charindex('-R',columnName)+1) >> >> "helpful sql" <nospam@stopspam.com> wrote in message >> news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... >>> select left(columnName, charindex('-R',columnName)+1), >>> MAX(substring(columnName,charindex('-R',columnName)+2,10)) >>> from tableName >>> group by left(columnName, charindex('-R',columnName)+1) >>> >>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>> news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >>>> Thank you, >>>> That worked great but I did not ask the full question. Maybe you can >>>> help again. >>>> >>>> if these strings are in a column >>>> 12345678-R101 >>>> 12345678-R201 >>>> 12345678-R301 >>>> >>>> 98564512-R112 >>>> >>>> 18752381-R101 >>>> 18752381-R201 >>>> >>>> 18752381-R110 >>>> >>>> 18752381-R111 >>>> 18752381-R211 >>>> >>>> If there is a -R2 there will always be a -R1. Likewise if there is >>>> a -R3 there will always be a -R2. >>>> In the above example I need to return >>>> 12345678-R301 >>>> >>>> 98564512-R112 >>>> >>>> 18752381-R201 >>>> >>>> 18752381-R110 >>>> >>>> 18752381-R211 >>>> >>>> I hope I have explained this well enough. >>>> >>>> Thanks, >>>> Bob Hiller >>>> Lifts for the Disabled LLC >>>> >>>> >>>> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >>>> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>>>> select right(columnName, 3), count(columnName) >>>>> from tableName >>>>> group by right(columnName, 3) >>>>> >>>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>>>I am trying to find a query to count rows with multiple conditions. >>>>>> >>>>>> I have files which are suffixed with -R and then a number from 1 to 3 >>>>>> and then two digits 00-14. >>>>>> the field is char >>>>>> >>>>>> i.e.: >>>>>> -R101 >>>>>> -R102 >>>>>> -R114 >>>>>> -R201 >>>>>> -R302 >>>>>> >>>>>> I need to determine how many 101, how many 102, etc through 114 and >>>>>> then the same for >>>>>> the 201-214 series and then >>>>>> the 301-314 series. >>>>>> >>>>>> I will be sending the query from Visual Basic using ADODC so I am not >>>>>> sure how the data will be returned. If it was written to a temp table >>>>>> that would be great. >>>>>> >>>>>> Thanks, >>>>>> Bob Hiller >>>>>> Lifts for the Disabled LLC >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > oj,
Thanks for the suggestion but the values where just given as samples. There are thousands of rows that I have to search through. I don't think this approach will work. Thanks, Bob Hiller Lifts for the Disabled LLC Show quote "oj" <nospam_ojngo@home.com> wrote in message news:e%23xZMS6TGHA.5108@TK2MSFTNGP11.phx.gbl... > select max(a) > from(select '12345678-R101' > union all select '12345678-R201' > union all select '12345678-R301' > union all select '98564512-R112' > union all select '18752381-R101' > union all select '18752381-R201' > union all select '18752381-R110' > union all select '18752381-R111' > union all select '18752381-R211')x(a) > group by left(a,8),right(a,2) > > > -- > -oj > > > "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message > news:Ow6lDS5TGHA.4600@TK2MSFTNGP11.phx.gbl... >> Ok, I can live with the 2 returned expressions, they will work fine. >> >> Here is what is returned when I run the sample: >> 12345678-R301 >> 98564512-R112 >> 18752381-R211 >> >> I am missing: >> 18752381-R201 >> 18752381-R110 >> >> Think of the first number after the -R as a counter for the last 2 >> numbers that represent a group. >> When these are in the table >> >> 18752381-R101 01 is the group and 1 is the counter >> 18752381-R201 01 is the group and 2 is the counter >> we want to return the largest counter for group 01 for the number to the >> left of -R >> return (18752381-R201) >> >> 18752381-R110 10 is the group and 1 is the counter >> return (18752381-R110) it is the only group 10 for the number to the >> left of -R >> >> 18752381-R111 11 is the group and 1 is the counter >> 18752381-R211 11 is the group and 2 is the counter >> return (18752381-R211) >> >> Thank in advance, >> Bob Hiller >> Lifts for the Disabled LLC >> >> >> >> "helpful sql" <nospam@stopspam.com> wrote in message >> news:OsGYae4TGHA.4792@TK2MSFTNGP14.phx.gbl... >>> OOPS... You need to convert the count values to int... >>> >>> select left(columnName, charindex('-R',columnName)+1), >>> MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) >>> from tableName >>> group by left(columnName, charindex('-R',columnName)+1) >>> >>> "helpful sql" <nospam@stopspam.com> wrote in message >>> news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... >>>> select left(columnName, charindex('-R',columnName)+1), >>>> MAX(substring(columnName,charindex('-R',columnName)+2,10)) >>>> from tableName >>>> group by left(columnName, charindex('-R',columnName)+1) >>>> >>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>> news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >>>>> Thank you, >>>>> That worked great but I did not ask the full question. Maybe you can >>>>> help again. >>>>> >>>>> if these strings are in a column >>>>> 12345678-R101 >>>>> 12345678-R201 >>>>> 12345678-R301 >>>>> >>>>> 98564512-R112 >>>>> >>>>> 18752381-R101 >>>>> 18752381-R201 >>>>> >>>>> 18752381-R110 >>>>> >>>>> 18752381-R111 >>>>> 18752381-R211 >>>>> >>>>> If there is a -R2 there will always be a -R1. Likewise if there is >>>>> a -R3 there will always be a -R2. >>>>> In the above example I need to return >>>>> 12345678-R301 >>>>> >>>>> 98564512-R112 >>>>> >>>>> 18752381-R201 >>>>> >>>>> 18752381-R110 >>>>> >>>>> 18752381-R211 >>>>> >>>>> I hope I have explained this well enough. >>>>> >>>>> Thanks, >>>>> Bob Hiller >>>>> Lifts for the Disabled LLC >>>>> >>>>> >>>>> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >>>>> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>>>>> select right(columnName, 3), count(columnName) >>>>>> from tableName >>>>>> group by right(columnName, 3) >>>>>> >>>>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>>>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>>>>I am trying to find a query to count rows with multiple conditions. >>>>>>> >>>>>>> I have files which are suffixed with -R and then a number from 1 to >>>>>>> 3 and then two digits 00-14. >>>>>>> the field is char >>>>>>> >>>>>>> i.e.: >>>>>>> -R101 >>>>>>> -R102 >>>>>>> -R114 >>>>>>> -R201 >>>>>>> -R302 >>>>>>> >>>>>>> I need to determine how many 101, how many 102, etc through 114 and >>>>>>> then the same for >>>>>>> the 201-214 series and then >>>>>>> the 301-314 series. >>>>>>> >>>>>>> I will be sending the query from Visual Basic using ADODC so I am >>>>>>> not sure how the data will be returned. If it was written to a temp >>>>>>> table that would be great. >>>>>>> >>>>>>> Thanks, >>>>>>> Bob Hiller >>>>>>> Lifts for the Disabled LLC >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Bob,
You need to adapt the technique to your data. e.g. select max(your_col) from tb group by left(your_col,8),right(your_col,2) If it does not give you the desired result, you'd want to post ddl + sample data + expected result here so we can help. -- Show quote-oj "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:uRMPBI8TGHA.5836@TK2MSFTNGP10.phx.gbl... > oj, > Thanks for the suggestion but the values where just given as samples. > There are thousands of rows that I have to search through. I don't think > this approach will work. > > Thanks, > Bob Hiller > Lifts for the Disabled LLC > > "oj" <nospam_ojngo@home.com> wrote in message > news:e%23xZMS6TGHA.5108@TK2MSFTNGP11.phx.gbl... >> select max(a) >> from(select '12345678-R101' >> union all select '12345678-R201' >> union all select '12345678-R301' >> union all select '98564512-R112' >> union all select '18752381-R101' >> union all select '18752381-R201' >> union all select '18752381-R110' >> union all select '18752381-R111' >> union all select '18752381-R211')x(a) >> group by left(a,8),right(a,2) >> >> >> -- >> -oj >> >> >> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >> news:Ow6lDS5TGHA.4600@TK2MSFTNGP11.phx.gbl... >>> Ok, I can live with the 2 returned expressions, they will work fine. >>> >>> Here is what is returned when I run the sample: >>> 12345678-R301 >>> 98564512-R112 >>> 18752381-R211 >>> >>> I am missing: >>> 18752381-R201 >>> 18752381-R110 >>> >>> Think of the first number after the -R as a counter for the last 2 >>> numbers that represent a group. >>> When these are in the table >>> >>> 18752381-R101 01 is the group and 1 is the counter >>> 18752381-R201 01 is the group and 2 is the counter >>> we want to return the largest counter for group 01 for the number to the >>> left of -R >>> return (18752381-R201) >>> >>> 18752381-R110 10 is the group and 1 is the counter >>> return (18752381-R110) it is the only group 10 for the number to the >>> left of -R >>> >>> 18752381-R111 11 is the group and 1 is the counter >>> 18752381-R211 11 is the group and 2 is the counter >>> return (18752381-R211) >>> >>> Thank in advance, >>> Bob Hiller >>> Lifts for the Disabled LLC >>> >>> >>> >>> "helpful sql" <nospam@stopspam.com> wrote in message >>> news:OsGYae4TGHA.4792@TK2MSFTNGP14.phx.gbl... >>>> OOPS... You need to convert the count values to int... >>>> >>>> select left(columnName, charindex('-R',columnName)+1), >>>> MAX(convert(int,substring(columnName,charindex('-R',columnName)+2,10))) >>>> from tableName >>>> group by left(columnName, charindex('-R',columnName)+1) >>>> >>>> "helpful sql" <nospam@stopspam.com> wrote in message >>>> news:OLQc8X4TGHA.424@TK2MSFTNGP12.phx.gbl... >>>>> select left(columnName, charindex('-R',columnName)+1), >>>>> MAX(substring(columnName,charindex('-R',columnName)+2,10)) >>>>> from tableName >>>>> group by left(columnName, charindex('-R',columnName)+1) >>>>> >>>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>>> news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... >>>>>> Thank you, >>>>>> That worked great but I did not ask the full question. Maybe you can >>>>>> help again. >>>>>> >>>>>> if these strings are in a column >>>>>> 12345678-R101 >>>>>> 12345678-R201 >>>>>> 12345678-R301 >>>>>> >>>>>> 98564512-R112 >>>>>> >>>>>> 18752381-R101 >>>>>> 18752381-R201 >>>>>> >>>>>> 18752381-R110 >>>>>> >>>>>> 18752381-R111 >>>>>> 18752381-R211 >>>>>> >>>>>> If there is a -R2 there will always be a -R1. Likewise if there is >>>>>> a -R3 there will always be a -R2. >>>>>> In the above example I need to return >>>>>> 12345678-R301 >>>>>> >>>>>> 98564512-R112 >>>>>> >>>>>> 18752381-R201 >>>>>> >>>>>> 18752381-R110 >>>>>> >>>>>> 18752381-R211 >>>>>> >>>>>> I hope I have explained this well enough. >>>>>> >>>>>> Thanks, >>>>>> Bob Hiller >>>>>> Lifts for the Disabled LLC >>>>>> >>>>>> >>>>>> "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message >>>>>> news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >>>>>>> select right(columnName, 3), count(columnName) >>>>>>> from tableName >>>>>>> group by right(columnName, 3) >>>>>>> >>>>>>> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >>>>>>> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>>>>>>I am trying to find a query to count rows with multiple conditions. >>>>>>>> >>>>>>>> I have files which are suffixed with -R and then a number from 1 to >>>>>>>> 3 and then two digits 00-14. >>>>>>>> the field is char >>>>>>>> >>>>>>>> i.e.: >>>>>>>> -R101 >>>>>>>> -R102 >>>>>>>> -R114 >>>>>>>> -R201 >>>>>>>> -R302 >>>>>>>> >>>>>>>> I need to determine how many 101, how many 102, etc through 114 and >>>>>>>> then the same for >>>>>>>> the 201-214 series and then >>>>>>>> the 301-314 series. >>>>>>>> >>>>>>>> I will be sending the query from Visual Basic using ADODC so I am >>>>>>>> not sure how the data will be returned. If it was written to a temp >>>>>>>> table that would be great. >>>>>>>> >>>>>>>> Thanks, >>>>>>>> Bob Hiller >>>>>>>> Lifts for the Disabled LLC >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > Thanks for all the help
Bob Hiller Lifts for the Disabled LLC Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:%23IRMFQ4TGHA.1204@TK2MSFTNGP12.phx.gbl... > Thank you, > That worked great but I did not ask the full question. Maybe you can help > again. > > if these strings are in a column > 12345678-R101 > 12345678-R201 > 12345678-R301 > > 98564512-R112 > > 18752381-R101 > 18752381-R201 > > 18752381-R110 > > 18752381-R111 > 18752381-R211 > > If there is a -R2 there will always be a -R1. Likewise if there is a -R3 > there will always be a -R2. > In the above example I need to return > 12345678-R301 > > 98564512-R112 > > 18752381-R201 > > 18752381-R110 > > 18752381-R211 > > I hope I have explained this well enough. > > Thanks, > Bob Hiller > Lifts for the Disabled LLC > > > "Raymond D'Anjou" <rdanjou@canatradeNOSPAM.com> wrote in message > news:uWek153TGHA.4540@TK2MSFTNGP10.phx.gbl... >> select right(columnName, 3), count(columnName) >> from tableName >> group by right(columnName, 3) >> >> "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message >> news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >>>I am trying to find a query to count rows with multiple conditions. >>> >>> I have files which are suffixed with -R and then a number from 1 to 3 >>> and then two digits 00-14. >>> the field is char >>> >>> i.e.: >>> -R101 >>> -R102 >>> -R114 >>> -R201 >>> -R302 >>> >>> I need to determine how many 101, how many 102, etc through 114 and then >>> the same for >>> the 201-214 series and then >>> the 301-314 series. >>> >>> I will be sending the query from Visual Basic using ADODC so I am not >>> sure how the data will be returned. If it was written to a temp table >>> that would be great. >>> >>> Thanks, >>> Bob Hiller >>> Lifts for the Disabled LLC >>> >>> >>> >>> >> >> > > Thanks for all the help
Bob Hiller Lifts for the Disabled LLC Show quote "Bob and Sharon Hiller" <aokl***@tir.com> wrote in message news:ecFHqw3TGHA.5464@TK2MSFTNGP10.phx.gbl... >I am trying to find a query to count rows with multiple conditions. > > I have files which are suffixed with -R and then a number from 1 to 3 and > then two digits 00-14. > the field is char > > i.e.: > -R101 > -R102 > -R114 > -R201 > -R302 > > I need to determine how many 101, how many 102, etc through 114 and then > the same for > the 201-214 series and then > the 301-314 series. > > I will be sending the query from Visual Basic using ADODC so I am not sure > how the data will be returned. If it was written to a temp table that > would be great. > > Thanks, > Bob Hiller > Lifts for the Disabled LLC > > > >  
Other interesting topics
|
|||||||||||||||||||||||
