help with a query

I need some help creating a QUERY..
here's an example of what I need to do: say for instance my database
contains 3 values:
4012345
3012345
4055555
I want to create a query that will count the number of records where
the last 5 are unique. In the example that count would equal 2. I won't
know what the last 5 digits are.
How would I go about this? My first though would be to use DISTINCT,
but I don't know how to format the SELECT DISINCT part of the query..
is there some kind of RIGHT(field,5) method I can use?
any help would be appreciated.SELECT COUNT(DISTINCT RIGHT(colname,5))
FROM TableName
On a big table, however, this will perform horribly, because every single
row will need to be passed through the string function.
If the right-most five digits of the number mean something special, then
perhaps you should consider storing those digits in a separate column.
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||>> is there some kind of RIGHT(field,5) method I can use?
Yes.
Anith|||Please post DDL along with the sample data and expected results, so we can
better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
How about this (completed untested, due to lack of DDL):
SELECT COUNT(*) AS Count of FROM
(SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
Do you find yourself needing to break out the right 4 digits on a regular
basis? If so, you might want to consider creating separate columns for the
leading digits and trailing digits - that way, you would not need to use
parsing functions and you could place appropriate indices on the columns to
be searched/summarized.
IHTH
Jeremy
<kevin@.questionmark.com> wrote in message
news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
> I need some help creating a QUERY..
> here's an example of what I need to do: say for instance my database
> contains 3 values:
> 4012345
> 3012345
> 4055555
> I want to create a query that will count the number of records where
> the last 5 are unique. In the example that count would equal 2. I won't
> know what the last 5 digits are.
> How would I go about this? My first though would be to use DISTINCT,
> but I don't know how to format the SELECT DISINCT part of the query..
> is there some kind of RIGHT(field,5) method I can use?
> any help would be appreciated.
>|||Sorry, for some reason I mistook your requirement as a need to split up a
phone number, so I used the right four characters instead of the right 5
characters - this should be better:
SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:eX8sSItlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with the sample data and expected results, so we can
> better answer your question [http://www.aspfaq.com/etiquette.asp?id=5006]
> How about this (completed untested, due to lack of DDL):
> SELECT COUNT(*) AS Count of FROM
> (SELECT DISTINCT(SUBSTRING(MadeUpColumn, 4, 4) FROM MadeUpTable) FakeTable
> Do you find yourself needing to break out the right 4 digits on a regular
> basis? If so, you might want to consider creating separate columns for the
> leading digits and trailing digits - that way, you would not need to use
> parsing functions and you could place appropriate indices on the columns
to
> be searched/summarized.
> IHTH
> Jeremy
> <kevin@.questionmark.com> wrote in message
> news:1122924156.826395.283970@.g49g2000cwa.googlegroups.com...
>|||> SELECT COUNT(DISTINCT(SUBSTRING(MadeUpColumn, 3, 5)) FROM MadeUpTable
Well, this assumes the value will always be exactly 8 digits... more sample
data would be useful, but the OP said right 5 characters...|||So what are you saying, I should have used RIGHT instead of SUBSTRING, just
because the OP said they wanted the right 5 characters? :-)
Just another example of "open mouth, insert foot". Thanks for the
correction, Aaron!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23pPg4btlFHA.3336@.tk2msftngp13.phx.gbl...
> Well, this assumes the value will always be exactly 8 digits... more
sample
> data would be useful, but the OP said right 5 characters...
>|||Well, I don't necessarily think your solution is bad (or constitutes
foot-in-mouth syndrome); I prefer substring myself. Just trying to pre-empt
the inevitable, "Hey, Jeremy, this only works on columns where the value has
exactly 8 digits! Thanks a LOT!" ;-)
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:urH3w3tlFHA.3316@.TK2MSFTNGP14.phx.gbl...
> So what are you saying, I should have used RIGHT instead of SUBSTRING,
> just
> because the OP said they wanted the right 5 characters? :-)
> Just another example of "open mouth, insert foot". Thanks for the
> correction, Aaron!
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. No wonder that you are mimicing
a COBOL program and file.
If these "last five digits" (are they CHAR(7) or INTEGER or what?)
have a meaning apart from the **column**, then they need to be modeled
as another data element.