2 Replies - 4502 Views - Last Post: 15 June 2008 - 02:04 AM Rate Topic: -----

#1 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

select sql-data on max(date) and equallity

Post icon  Posted 14 June 2008 - 02:47 PM

Hello,

Let's say I have a table with the following data-colums:

ID, Name, Company, date.

Now I wanna select from each company the name and date where the column date contains the last date.

I have the following code:

select b.Name, b.Date from #templijst b where date = (SELECT MAX(Date) FROM #templijst)
group by Name, Date 



But this selects only the row with the last date. Not from each company??

Please Help Me!!

Is This A Good Question/Topic? 0
  • +

Replies To: select sql-data on max(date) and equallity

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4189
  • View blog
  • Posts: 11,864
  • Joined: 18-April 07

Re: select sql-data on max(date) and equallity

Posted 14 June 2008 - 04:11 PM

This should solve your problem... in the example below if your table was named "Test" and your date column was named "thedate"...

SELECT Test.Id, Test.name, Test.company, Test.thedate
FROM Test INNER JOIN
(SELECT company, MAX(thedate) AS thedate
FROM Test GROUP BY company) results ON Test.company = results.company AND Test.thedate = results.thedate



What we are doing is first creating an inner select which groups on "COMPANY" and the resulting table is named "results". This will give you the max date for each company.

We then create an outer select where we specify which fields we want to display. Here we are displaying all 4 fields but you could easily just list name and thedate. We then link "Test" to our "results" subquery and find any rows where the company name = the company name from our results AND where Test "thedate" = results "thedate". Since the date will be unique for each given company, we are essentially using them as the criteria to filter the Test table.

The result is each record with each single company and their max date.

Hope this makes sense. Enjoy!

"At DIC we be query filtering code ninjas... we also filter our vodka through a block of ice sculpted to look like an Apple IIe" :snap:
Was This Post Helpful? 0
  • +
  • -

#3 Hanzie  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 128
  • Joined: 19-August 07

Re: select sql-data on max(date) and equallity

Posted 15 June 2008 - 02:04 AM

Thnx, this is great! It works! I didn't know it needed that much coding to get these results.

Can you recommend a book that really gets into sql-coding. I know the simple select-query's but now i need to make a step furher in this.

Thnx in advanced! You guys are great!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1