Thread: query
Hello, <br /><br />I have a table in which I have a field with format like 100101. It has many values like 100101, 100102,100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that theycontain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not bothi.e if the last two characters are not same it should not return both of them. It should return only values startingwith 10 the middle two values should be distinct and the last two characters may be anything. <br /><hr align="left"width="300" /> View this message in context: <a href="http://www.nabble.com/query-t1074932.html#a2798161">query</a><br/> Sent from the <a href="http://www.nabble.com/PostgreSQL---sql-f777.html">PostgreSQL- sql</a> forum at Nabble.com.
superboy143 (sent by Nabble.com) wrote: > Hello, > > I have a table in which I have a field with format like 100101. It > has many values like 100101, 100102, 100103, 100201, 100202, 100301. OK - so they look like numbers but aren't. > I have to write a query such that I have to get only distinct values > such that they contain only the substring I need. If I give 10 as > substring, then it should return only 100101 or 100102 but not both > i.e if the last two characters are not same it should not return both > of them. The statement of the rule and the example contradict each other. Surely 100101 and 100102 are distinct values. Surely they contain "10" as a substring. So according to your rule the should be returned. > It should return only values starting with 10 the middle two > values should be distinct and the last two characters may be > anything. This seems to agree with your example and not your rule. So: 1. You don't want distinct values of your field, you want distinct substrings of your field? 2. You don't want to match a substring, you want to match the start of the string? 3. You want only one of 100101 or 100102 but you don't care which? Is this correct? By the way - it looks to me like you are trying to store multiple values in one column. If you split the values into their own columns I'd guess your query would be much easier. -- Richard Huxton Archonet Ltd
On Tue, Feb 07, 2006 at 01:45:50 -0800, "superboy143 (sent by Nabble.com)" <lists@nabble.com> wrote: > > I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201,100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only thesubstring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the lasttwo characters are not same it should not return both of them. It should return only values starting with 10 the middletwo values should be distinct and the last two characters may be anything. You can probably use the Postgres extension DISTINCT ON to do what you want.
On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote: <blockquote type="CITE"><pre> <font color="#000000">On Tue, Feb 07, 2006 at 01:45:50 -0800,</font> <font color="#000000"> "superboy143 (sent by Nabble.com)" <<a href="mailto:lists@nabble.com">lists@nabble.com</a>>wrote:</font> <font color="#000000">> </font> <font color="#000000">> I have a table in which I have a field with format like 100101. It has many values like 100101,100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values suchthat they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 butnot both i.e if the last two characters are not same it should not return both of them. It should return only values startingwith 10 the middle two values should be distinct and the last two characters may be anything.</font> <font color="#000000">You can probably use the Postgres extension DISTINCT ON to do what you want.</font> <font color="#000000">---------------------------(end of broadcast)---------------------------</font> <font color="#000000">TIP 3: Have you checked our extensive FAQ?</font> <font color="#000000"> <a href="http://www.postgresql.org/docs/faq">http://www.postgresq</a></font>l.org/docs/faq </pre></blockquote><br /> Try substring(column,1,4). That should return values of 1001, 1002, 1003 when grouped.