I have a table Names with two fields:
ChID varchar(1)
Name varchar(25)
where the ChID char is in a range a .. z
(there will never be more then 27 records)
Some chars are used, some not
Two examples:
eg.
I have 4 records: CharID b, c, e, and f are used
I want to find the first not used char
In this example it is the char a
eg.
I have 5 records: CharID a, b, c, e, and f are used
I want to find the first not used char
In this example it is the char d.
Is a SELECT statement possible for finding the first not used Char?
How would such a statement be?Hi Henk,
Assuming all your ChID's are lower case, and a-z in ASCII:
CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('f')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('c')
INSERT INTO Names(ChID) VALUES('e')
SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) =ASCII(Names.ChID)-1 )
AND ChID > 'a'
DROP TABLE Names
How do you get _27_ rows btw?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> I have a table Names with two fields:
> ChID varchar(1)
> Name varchar(25)
> where the ChID char is in a range a .. z
> (there will never be more then 27 records)
> Some chars are used, some not
> Two examples:
> eg.
> I have 4 records: CharID b, c, e, and f are used
> I want to find the first not used char
> In this example it is the char a
> eg.
> I have 5 records: CharID a, b, c, e, and f are used
> I want to find the first not used char
> In this example it is the char d.
> Is a SELECT statement possible for finding the first not used Char?
> How would such a statement be?
>|||Jacco thanks,
It is a wonderful simple solution, exactly what I wanted.
You asked: How do you get _27_ rows btw?
Do you mean: How do you control that there will not be more then 27 rows?
The solution is that I use this SQL statement in a Delphi application. There
I use RecordCount, to limit the amount of records.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hi Henk,
>
> Assuming all your ChID's are lower case, and a-z in ASCII:
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('f')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('e')
> SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)-1 )
> AND ChID > 'a'
> DROP TABLE Names
> How do you get _27_ rows btw?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > I have a table Names with two fields:
> > ChID varchar(1)
> > Name varchar(25)
> > where the ChID char is in a range a .. z
> > (there will never be more then 27 records)
> > Some chars are used, some not
> >
> > Two examples:
> > eg.
> > I have 4 records: CharID b, c, e, and f are used
> > I want to find the first not used char
> > In this example it is the char a
> > eg.
> > I have 5 records: CharID a, b, c, e, and f are used
> > I want to find the first not used char
> > In this example it is the char d.
> >
> > Is a SELECT statement possible for finding the first not used Char?
> > How would such a statement be?
> >
> >
>|||the previous code won't work in case 'abc' (must be 'd') and in case of
empty table (must be 'a'). this one looks not so fine, but works
declare @.id varchar(1), @.fo varchar(1)
set @.fo='a'
declare MV cursor for select distinct lower(ChID) from Names where ChID
between 'a' and 'z' order by 1
Open MV
FETCH NEXT FROM MV INTO @.id
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.fo<>@.id BREAK
set @.fo=(CHAR(ASCII(@.fo) + 1))
FETCH NEXT FROM MV INTO @.id
END
CLOSE MV
DEALLOCATE MV
IF ASCII(@.fo)>122 set @.fo=NULL
print ISNULL(@.fo,'-')|||Hi Henk,
I meant that usually there are only 26 letters from a-z (well, definitly in
ASCII), so I was wondering where you got the 27th from?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%238Xc8$wdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> Jacco thanks,
> It is a wonderful simple solution, exactly what I wanted.
> You asked: How do you get _27_ rows btw?
> Do you mean: How do you control that there will not be more then 27 rows?
> The solution is that I use this SQL statement in a Delphi application.
There
> I use RecordCount, to limit the amount of records.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
> news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> > Hi Henk,
> >
> >
> > Assuming all your ChID's are lower case, and a-z in ASCII:
> >
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> >
> > INSERT INTO Names(ChID) VALUES('f')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('e')
> >
> > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => > ASCII(Names.ChID)-1 )
> > AND ChID > 'a'
> >
> > DROP TABLE Names
> >
> > How do you get _27_ rows btw?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > I have a table Names with two fields:
> > > ChID varchar(1)
> > > Name varchar(25)
> > > where the ChID char is in a range a .. z
> > > (there will never be more then 27 records)
> > > Some chars are used, some not
> > >
> > > Two examples:
> > > eg.
> > > I have 4 records: CharID b, c, e, and f are used
> > > I want to find the first not used char
> > > In this example it is the char a
> > > eg.
> > > I have 5 records: CharID a, b, c, e, and f are used
> > > I want to find the first not used char
> > > In this example it is the char d.
> > >
> > > Is a SELECT statement possible for finding the first not used Char?
> > > How would such a statement be?
> > >
> > >
> >
> >
>|||Sorry for the crosspost,
I also found out that the answer is not always correct.
(also thanks to news.rinet from russia)
I study this code to see if its better then the code I posted.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:uyhXeT3dDHA.2816@.TK2MSFTNGP10.phx.gbl...
> Hi Henk,
> As pointed out in the other post it didn't work in all situations, but I
> have made the corrections in the code below.
> What the code now does is first check if there is a row that is 'a' (WHEN
> NOT EXISTS(SELECT NULL FROM Names n2 WHERE ChID = 'a') THEN 'a') , and if
> there isn't than that is of course the value you're looking for. This also
> catches the empty table. Next it will find the lowest ChID for which the
> next highest ChID in alphabetical order doesn't exists.
> You can also use a table variable with a-z in it and compare against that
> (second script)
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('a')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('d')
> SELECT CASE WHEN NOT EXISTS(SELECT NULL FROM Names n2 WHERE ChID = 'a')
THEN
> 'a' ELSE
> MIN(CHAR(ASCII(ChID) + 1))
> END
> FROM Names
> WHERE
> NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)+1 )
> DROP TABLE Names
>
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('a')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('f')
> INSERT INTO Names(ChID) VALUES('d')
> DECLARE @.letters TABLE(letter char(1))
> DECLARE @.i TINYINT
> SET @.i = 97
> WHILE @.i < 123
> BEGIN
> INSERT INTO @.letters(letter) VALUES(CHAR(@.i))
> SET @.i = @.i +1
> END
> SELECT MIN(letter)
> FROM @.letters l
> LEFT OUTER JOIN Names n
> ON l.letter = n.ChID
> WHERE n.ChID IS NULL
>
> DROP TABLE Names
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:eIkC4dxdDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > Jacco after studying your code, I must say it is not so simple as I
first
> > thought.
> > I do not understand the working of this piece of art yet, but I'am
trying.
> >
> > Oh, btw, a..z is 26 chars, not 27. I have to go to the primary school
> again
> > <g>.
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
> > news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> >
> > > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > > WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => > > ASCII(Names.ChID)-1 )
> > > AND ChID > 'a'
> > >
> > > How do you get _27_ rows btw?
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > I have a table Names with two fields:
> > > > ChID varchar(1)
> > > > Name varchar(25)
> > > > where the ChID char is in a range a .. z
> > > > (there will never be more then 27 records)
> > > > Some chars are used, some not
> > > >
> > > > Two examples:
> > > > eg.
> > > > I have 4 records: CharID b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char a
> > > > eg.
> > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char d.
> > > >
> > > > Is a SELECT statement possible for finding the first not used Char?
> > > > How would such a statement be?
> >
> >
> >
>|||I'am sorry that I have to tell you that the sql statement did'nt give the
correct answer.
Example:
CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('f')
INSERT INTO Names(ChID) VALUES('a')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('c')
SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
WHERE NOT EXISTS(SELECT NULL FROM Names n2
WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
AND ChID > 'a'
DROP TABLE Names
The result is an 'e' in stead of 'd' (the first free char)
But you showed me a way to handle this problem (thanks for that).
I made this statement:
CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('c')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('a')
INSERT INTO Names(ChID) VALUES('z')
SELECT ISNULL(MIN(Nw), 'a') FROM (
SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
UNION
SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names) Tmp
DROP TABLE Names
It gives the correct answers (the ISNULL is for a empty table)
but the code is not very well designed, using a UNION.
Is it possible to get the same result without a union?
Henk.
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > I have a table Names with two fields:
> > > > ChID varchar(1)
> > > > Name varchar(25)
> > > > where the ChID char is in a range a .. z
> > > > (there will never be more then 27 records)
> > > > Some chars are used, some not
> > > >
> > > > Two examples:
> > > > eg.
> > > > I have 4 records: CharID b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char a
> > > > eg.
> > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char d.
> > > >
> > > > Is a SELECT statement possible for finding the first not used Char?
> > > > How would such a statement be?|||Hi Henk,
The WHERE clause you have posted here
WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
is logically equivalent to the WHERE clause in my post in the other part of
this thread
WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) =ASCII(Names.ChID)+1)
I know that using IN is easier to understand than using EXISTS, but I advise
you to try to understand the use of EXISTS, because IN is limited to an
equality on one column, where with EXISTS you can use multiple columns and
different operators (<, >, BETWEEN, etc). On top of that EXISTS will always
perform as least as well as IN, and often it performs a lot better.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:uDQfLY5dDHA.3584@.tk2msftngp13.phx.gbl...
> I made a syntesis of your and my statements:
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('d')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('z')
> SELECT CASE
> WHEN NOT EXISTS (SELECT NULL FROM Names WHERE ChID = 'a') THEN 'a'
> ELSE CHAR(MIN(ASCII(ChID) + 1)) END
> FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
> I think this is the most elegant solution.
> Thanks a lot.
> Henk
> "Henk Schreij" <henk@.schreijDOTnl> schreef in bericht
> news:OPJslo4dDHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I'am sorry that I have to tell you that the sql statement did'nt give
the
> > correct answer.
> >
> > Example:
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('f')
> > INSERT INTO Names(ChID) VALUES('a')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> >
> > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > WHERE NOT EXISTS(SELECT NULL FROM Names n2
> > WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
> > AND ChID > 'a'
> >
> > DROP TABLE Names
> >
> > The result is an 'e' in stead of 'd' (the first free char)
> >
> > But you showed me a way to handle this problem (thanks for that).
> >
> > I made this statement:
> > CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('a')
> > INSERT INTO Names(ChID) VALUES('z')
> >
> > SELECT ISNULL(MIN(Nw), 'a') FROM (
> > SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
> > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
> Names)
> > UNION
> > SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names)
Tmp
> >
> > DROP TABLE Names
> >
> > It gives the correct answers (the ISNULL is for a empty table)
> > but the code is not very well designed, using a UNION.
> >
> > Is it possible to get the same result without a union?
> >
> > Henk.
> > > > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > > > I have a table Names with two fields:
> > > > > > ChID varchar(1)
> > > > > > Name varchar(25)
> > > > > > where the ChID char is in a range a .. z
> > > > > > (there will never be more then 27 records)
> > > > > > Some chars are used, some not
> > > > > >
> > > > > > Two examples:
> > > > > > eg.
> > > > > > I have 4 records: CharID b, c, e, and f are used
> > > > > > I want to find the first not used char
> > > > > > In this example it is the char a
> > > > > > eg.
> > > > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > > > I want to find the first not used char
> > > > > > In this example it is the char d.
> > > > > >
> > > > > > Is a SELECT statement possible for finding the first not used
> Char?
> > > > > > How would such a statement be?
>
>|||Jacco, thank you for this extra explanation.
I'll study the Exists in my SQL-book and try to use it more often, as it has
indeed some benefits over the IN statement.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:#8QRez5dDHA.1460@.TK2MSFTNGP10.phx.gbl...
> Hi Henk,
> The WHERE clause you have posted here
> WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
> is logically equivalent to the WHERE clause in my post in the other part
of
> this thread
> WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)+1)
> I know that using IN is easier to understand than using EXISTS, but I
advise
> you to try to understand the use of EXISTS, because IN is limited to an
> equality on one column, where with EXISTS you can use multiple columns and
> different operators (<, >, BETWEEN, etc). On top of that EXISTS will
always
> perform as least as well as IN, and often it performs a lot better.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:uDQfLY5dDHA.3584@.tk2msftngp13.phx.gbl...
> > I made a syntesis of your and my statements:
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('d')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('z')
> >
> > SELECT CASE
> > WHEN NOT EXISTS (SELECT NULL FROM Names WHERE ChID = 'a') THEN 'a'
> > ELSE CHAR(MIN(ASCII(ChID) + 1)) END
> > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
Names)
> >
> > I think this is the most elegant solution.
> > Thanks a lot.
> > Henk
> >
> > "Henk Schreij" <henk@.schreijDOTnl> schreef in bericht
> > news:OPJslo4dDHA.3992@.TK2MSFTNGP11.phx.gbl...
> > > I'am sorry that I have to tell you that the sql statement did'nt give
> the
> > > correct answer.
> > >
> > > Example:
> > > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > > INSERT INTO Names(ChID) VALUES('f')
> > > INSERT INTO Names(ChID) VALUES('a')
> > > INSERT INTO Names(ChID) VALUES('b')
> > > INSERT INTO Names(ChID) VALUES('c')
> > >
> > > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > > WHERE NOT EXISTS(SELECT NULL FROM Names n2
> > > WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
> > > AND ChID > 'a'
> > >
> > > DROP TABLE Names
> > >
> > > The result is an 'e' in stead of 'd' (the first free char)
> > >
> > > But you showed me a way to handle this problem (thanks for that).
> > >
> > > I made this statement:
> > > CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
> > > INSERT INTO Names(ChID) VALUES('c')
> > > INSERT INTO Names(ChID) VALUES('b')
> > > INSERT INTO Names(ChID) VALUES('a')
> > > INSERT INTO Names(ChID) VALUES('z')
> > >
> > > SELECT ISNULL(MIN(Nw), 'a') FROM (
> > > SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
> > > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
> > Names)
> > > UNION
> > > SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names)
> Tmp
> > >
> > > DROP TABLE Names
> > >
> > > It gives the correct answers (the ISNULL is for a empty table)
> > > but the code is not very well designed, using a UNION.
> > >
> > > Is it possible to get the same result without a union?
> > >
> > > Henk.
> > > > > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > > > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > > > > I have a table Names with two fields:
> > > > > > > ChID varchar(1)
> > > > > > > Name varchar(25)
> > > > > > > where the ChID char is in a range a .. z
> > > > > > > (there will never be more then 27 records)
> > > > > > > Some chars are used, some not
> > > > > > >
> > > > > > > Two examples:
> > > > > > > eg.
> > > > > > > I have 4 records: CharID b, c, e, and f are used
> > > > > > > I want to find the first not used char
> > > > > > > In this example it is the char a
> > > > > > > eg.
> > > > > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > > > > I want to find the first not used char
> > > > > > > In this example it is the char d.
> > > > > > >
> > > > > > > Is a SELECT statement possible for finding the first not used
> > Char?
> > > > > > > How would such a statement be?
> >
> >
> >
>
technique:
Antony Kumar wrote: