Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts

    SQL on MS SQL Server using DISTINCT clause

    I'm migrating a program from PHP/mySQL to PHP/SQL Server (not my idea) and am having some minor problems with the SQL.

    I've got a table (results) set up as follows...
    questionID int not null,
    resultText varchar(255) not null,
    lastUpdated datetime

    Now, on mySQL I ran this query...

    SELECT DISTINCT resultText FROM results ORDER BY lastUpdated

    which worked great.

    On SQL Server, appearenlty you need to include the filed which you want to order by in the select statement.

    IE my new query is...

    SELECT DISTINCT resultText, lastUpdated FROM results ORDER BY lastUpdated.

    Since lastUpdated is almost always unique, I get the entire table returned.

    All I want is the unique results from the resutls table ordered by the lastUpdated column. Any ideas?

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try:

    select distinct resulttext from (select resulttext,lastupdated from results order by lastupdated)

    I've got a similar problem on a much more complex query in a program I'm working on right now so this is a familiar problem .
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #3
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Thanks, that led me in the right direction.

    Here's the query that worked, hopefully it will help you out with your problem as well...

    SELECT DISTINCT resultText FROM (SELECT TOP 50000000 resultText, lastUpdated FROM s_results_text ORDER BY lastUpdated) DERIVEDTBL

    Good luck


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •