Stupid query question...

• Goober

  SSC Enthusiast

  Points: 177

  More actions

  September 22, 2006 at 12:32 pm

  #174165

  Hola...

  Trying to write a query that is giving me difficulty.  I would like the last record entered by date for several criteria.  Example:  I want to know the most recent pages sent to 3 different people.  Here is what I have so far:

  SELECT TOP 1 startdate, starttime, page, pageid FROM Page_History WHERE pageid = 'John' ORDER BY startdate ASC, starttime ASC

  Obviosly this pulls the recent entry for one person.  How do I do it for multiple Pageid's without having to write multiple queries?

   

  Thank you for any help you provide...

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 22, 2006 at 12:54 pm

  #662028

  The only stupid question is the question not asked.

  Try this and see if it gives you what you are looking for:

  SELECT -- TOP 1

      startdate,

      starttime,

      page,

      pageid

  FROM

      dbo.Page_History pgh1

  WHERE

      exists (    select

                      1

                  from

                      dbo.Page_History pgh2

                  where

                      pgh1.startdate = max(pgh2.startdate)

                      and pgh1.starttime = max(pgh2.starttime)

                      and pgh1.pageid = pgh2.pageid)

  hth,

  

   

• Goober

  SSC Enthusiast

  Points: 177

  More actions

  September 22, 2006 at 1:04 pm

  #662031

  Hmmm...  Not working.

  What are the pgh1 and pgh2?  Am I supposed to substitute anything here?

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 22, 2006 at 2:09 pm

  #662033

  Try this:

  Option 1: Max Page By PageID

  Select Page_History.startdate, Page_History.starttime, Page_History.page, Page_History.pageid 

   from dbo.Page_History Page_History INNER JOIN (

   Select pageid,max(startdate) as MaxStartDate ,max(StartTime) as MaxStartTime

    from dbo.Page_History pgh2

   group by pageid ) MaxPage on dbo.Page_History.pageid= MaxPage.pageid

    and Page_History.startdate= MaxPage.MaxStartDate

    and Page_History.StartTime= MaxPage.MaxStartTime

  Option 2: Max Page By Day By PageID

  Select Page_History.startdate, Page_History.starttime, Page_History.page, Page_History.pageid 

   from dbo.Page_History Page_History INNER JOIN (

   Select pageid,Cast(Convert(Char(10),startdate,101) as Datetime) as StartDate ,max(StartTime) as MaxStartTime

    from dbo.Page_History pgh2

   group by pageid,Cast(Convert(Char(10),startdate,101) as Datetime)

     ) MaxByDayPage on Page_History.pageid= MaxByDayPage.pageid

    and Page_History.startdate= MaxByDayPage.StartDate

    and Page_History.StartTime= MaxByDayPage.MaxStartTime

  Thanks

  Sreejith

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 22, 2006 at 2:17 pm

  #662034

  pgh1 and pgh2 are aliases for the tables in the queries.

  One thing that would help would be to have the table structures and some sample data to work with and the expected output.  A little difficult to come up with a possible solution in a vacuum.

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 22, 2006 at 2:23 pm

  #662037

  try this also

  Select max(startdate) as startdate, max(starttime) as starttime, max(page) as page, p.pageid FROM Page_History P

  Inner Join

  (select pageid,max(startdate) as maxdate from Page_History Group by pageid) B

  on p.startdate=b.maxdate and p.pageid=b.pageid

  Group by p.pageid

   

• Goober

  SSC Enthusiast

  Points: 177

  More actions

  September 22, 2006 at 3:24 pm

  #662045

  Gopi Nath Muluka,

  Thank you! Your script worked like a champ...

  Thanks again everyone for responding so quickly...