Re: Basic SQL


"Stefan Rybacki" <stefan.rybacki@xxxxxxx> wrote in message news:3jmqafFq20mvU1@xxxxxxxxxxxxxxxxx
> John Gilson wrote:
> > "Stefan Rybacki" <stefan.rybacki@xxxxxxx> wrote in message news:3jlhr8Fqd4cmU2@xxxxxxxxxxxxxxxxx
> >
> >>John Gilson wrote:
> >>
> >>>"MS" <mesemailoffers@xxxxxxxxxxx> wrote in message
> >>>news:1121280375.735695.277470@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> >>>
> >>>
> >>>>I am trying to write a sql statement that gives me the records that
> >>>>have multiple values for column b, but cannot figure it out - can
> >>>>someone help?
> >>>>
> >>>>My table is as follows:
> >>>>
> >>>>ID Value
> >>>>1234 S
> >>>>1234 S
> >>>>1234 X
> >>>>4444 S
> >>>>5555 X
> >>>>5555 Y
> >>>>
> >>>>I want to see what records have two distinct values for the "Value"
> >>>>column. The results of the query when running against the above data
> >>>>should be as follows:
> >>>>
> >>>>ID Value
> >>>>1234 S
> >>>>1234 X
> >>>>5555 Y
> >>>>5555 X
> >>>>
> >>>>Any help would be appreciated.
> >>>>
> >>>>Thanks!
> >>>
> >>>
> >>>SELECT ID, Value
> >>>FROM (SELECT DISTINCT id, value FROM T) AS T
> >>>WHERE EXISTS (SELECT * FROM T AS T1
> >>> WHERE T1.ID = T.ID AND
> >>> T1.Value <> T.Value)
> >>>
> >>>--
> >>>JAG
> >>>
> >>>
> >>
> >>Ui two subselects? :/ I even don't like the one I used in my solution ;)
> >>
> >>Regards
> >>Stefan
> >
> >
> > Given the original poster's stated input and required output, your query doesn't
> > do the trick.
> >
> > --
> > JAG
> >
> >
> Ok you're right, didn't recognize that the op wanted to get distinct values. But one
> subselect is still enough
>
> SELECT DISTINCT ID,VALUE FROM table2 T2 WHERE EXISTS (SELECT * FROM table2 WHERE ID=t2.ID
> AND VALUE<>T2.VALUE)
>
> Regards
> Stefan

Actually, my query uses a derived table (in the FROM clause) and a
subquery (in the WHERE clause). My query, conceptually, first gets
distinct values and then restricts while yours restricts on the full table
and then takes distinct values. Which is more efficient? Of course,
that depends on the particular DBMS. "You say potato and I say...".

--
JAG


.

Relevant Pages

  • Re: Difference in Query Result Using Left Outer Join
    ... > in that it restricts the records after the Join is performed. ... SET ROWCOUNT specifies a "physical" limit. ... The WHERE clause is a *logical* restriction. ... >> If you were to run the 2 queries in Query Analyzer with Display Execution ...
    (microsoft.public.sqlserver.programming)
  • Re: Basic SQL
    ... >> subquery. ... My query, conceptually, first gets ... >> JAG ... the same caveat applies regarding testing on the target DBMS. ...
    (comp.databases)
  • Re: Basic SQL
    ... Regards ... Given the original poster's stated input and required output, your query doesn't ... subquery (in the WHERE clause). ... distinct values and then restricts while yours restricts on the full table ...
    (comp.databases)
  • Re: Basic SQL
    ... Regards ... Given the original poster's stated input and required output, your query doesn't ... subquery (in the WHERE clause). ... distinct values and then restricts while yours restricts on the full table ...
    (comp.databases)
  • Re: Basic SQL
    ... >>>I am trying to write a sql statement that gives me the records that ... >> JAG ... Given the original poster's stated input and required output, your query doesn't ... Prev by Date: ...
    (comp.databases)