Results 1 to 9 of 9

Thread: Query help

  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Query help

    Hi, I am using MS SQL Server 2005 and VBscript to create an automatic task.

    I am using the following query:

    select distinct name, time from report order by time desc

    This query returns the right amount of lines but not all the columns in the line.

    i.e. It only returns column "name" and "time". How can I display all columns for the rows in the "distinct" above?

    I have tried:

    select * from report where name in (select distinct name, time from report order by time desc)

    But gives error:

    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

    I then tried:

    select * from report where name in (select top 1 name, time from report order by time desc)

    But gives error:

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Thank you in advance for your help.

    Yuri
    Last edited by yurimelo; 06-22-09 at 13:16.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select * from report order by time desc
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Thanks blindman for your help.

    That query will return all items in the table ordered by time.

    What I would like is to have only the latest items (most recent by time) hence why I used the distinct....

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    WITH helpTable as 
    (select name_, 
    	time_,
    	row_number() over (partition by name_ order by time_ desc) as rowNr
    from report
    )
    select name_, time_ from helpTable where rowNr = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Hi wim,

    Thanks for your reply but unfortunately my results may return multiple rowNr=1 so that wont work.

    Lets see if I can explain better....

    If I do this query:

    select solution,phase,updper,scope,variant,curncy,consoti me
    from cubereport
    group by solution, phase, updper, scope, variant, curncy, consotime

    I get 29 results. Great because they are the results I want. Only that I have some columns missing.


    So i tried this to add the other columns:

    Select cr.solution,cr.phase,cr.updper,cr.scope,cr.variant ,cr.curncy,cr.consotime,cr.consostatus,cr.partitio ntime,cr.status
    FROM cubereport cr,
    (select solution,phase,updper,scope,variant,curncy,consoti me from cubereport
    group by solution, phase, updper, scope, variant, curncy, consotime) rs
    Where cr.solution=rs.solution AND cr.phase=rs.phase AND cr.updper=rs.updper AND cr.scope=rs.scope
    AND cr.variant=rs.variant AND cr.curncy=rs.curncy AND cr.consotime=rs.consotime

    But no luck as now it returns 291 results...

    Any help will be appreciated.

    Yuri

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this should give you the exact same 29 rows you want, with the extra columns tossed in for free...
    Code:
    SELECT solution
         , phase
         , updper
         , scope
         , variant
         , curncy
         , consotime     
         , MIN(consostatus)
         , MIN(partitiontime)
         , MIN(status)
      FROM cubereport
    GROUP 
        BY solution
         , phase
         , updper
         , scope
         , variant
         , curncy
         , consotime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2009
    Posts
    5
    Hey r937,

    That is absolutely genious! It works just fine. I guess I was making something simple rather complicated.

    Thank you very much for your help.

    I wonder though if I change the MIN to MAX or AVG I suppose my results will remain unchanged as there is only one correspondent consostatus, patitiontime and status on the table.

    What do you think?

    Yuri

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by yurimelo
    I wonder though if I change the MIN to MAX or AVG I suppose my results will remain unchanged as there is only one correspondent consostatus, patitiontime and status on the table.
    if you knew this, then why bother wanting them returned by the query at all?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2009
    Posts
    5
    Well because they will remain unchange for one solution but will change for different phases etc...

Posting Permissions

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