Programming Help

Web Development



What's Here?

  • Members: 136,504
  • Replies: 479,558
  • Topics: 74,732
  • Snippets: 2,567
  • Tutorials: 677
  • Total Online: 1,717
  • Members: 104
  • Guests: 1,613

Welcome to Dream.In.Code
Getting Help is Easy!

Join 136,504 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,717 people online right now. Registration is fast and FREE... Join Now!




inner join on max(date)

 
 Reply to this topicStart new topic

inner join on max(date)
Hanzie
12 Aug, 2008 - 04:03 AM
Post #1

D.I.C Head
**

Joined: 19 Aug, 2007
Posts: 92


My Contributions
Hello, I have the following code with the following results:

CODE

Select a.bladnummer, a.naam, max(a.datum), a.datumid from

(SELECT tekeningen.bladnummer, tekeningen.naam, max(datums.datum) as Datum, datums.datumid from tekeningen
inner join datums on tekeningen.tekeningid=datums.tekeningid where tekeningen.bedrijvenid=102
group by tekeningen.bladnummer,tekeningen.naam, datums.datumid) a

group by a.bladnummer, a.naam, a.datumid
order by a.bladnummer


Bladnummer Naam Datum DatumID
1 K1 Palenplan 2008-02-29 232
2 K1 Palenplan 2008-03-07 241
3 K2 Beganegrond 2008-02-29 233
4 K2 Beganegrond 2008-03-07 240
5 K3 1e verdvloer 2008-02-29 234
6 K4 2e verdvloer 2008-02-29 235
7 K5 3e verdvloer 2008-02-29 236
8 K6 Dakvloer 2008-02-29 237

As you can see all dates in the column "Datum" are 2008-02-29 except two. These other two are with the date 2008-03-07.
I selected on max(date) but because these two dates have another value in the column "DatumID" it also shows the lower date.

My selectquery must only show the latest dates, so the value from row 1 and 3 i don't wan't to get shown!

I believe this must be easy, but i don't now how!!

Please help!
User is offlineProfile CardPM
+Quote Post

Hanzie
RE: Inner Join On Max(date)
12 Aug, 2008 - 05:09 AM
Post #2

D.I.C Head
**

Joined: 19 Aug, 2007
Posts: 92


My Contributions
Hello, I solved it myself with the following code with help of temporary table:

CODE

drop table #temptable1

SELECT tekeningen.bladnummer, tekeningen.naam, max(datums.datum) as Datum, datums.datumid into #temptable1 from tekeningen
inner join datums on tekeningen.tekeningid=datums.tekeningid where tekeningen.bedrijvenid=102
group by tekeningen.bladnummer,tekeningen.naam, datums.datumid

Select a.bladnummer, a.naam, a.datum, a.datumid
      from #temptable1 a, (select max(datum) as maxdate, bladnummer, naam from #temptable1 GROUP BY bladnummer, naam) b  
where a.bladnummer = b.bladnummer AND a.naam = b.naam AND a.datum = b.maxdate
order by a.bladnummer, a.naam


But maybe someone nows a simpler way?! THERE MUST BE!
User is offlineProfile CardPM
+Quote Post

baavgai
RE: Inner Join On Max(date)
12 Aug, 2008 - 05:48 AM
Post #3

Dreaming Coder
Group Icon

Joined: 16 Oct, 2007
Posts: 2,031



Thanked: 105 times
Dream Kudos: 475
Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
You're not just choosing by max, but a couple other criteria than spans the two tables. I'm afraid your solution is probably the best available, given the data.

You might consider putting the whole thing is a stored procedure.

CODE

create procedure getDatSummary
    @bedrijvenid int
as begin
    SELECT tekeningen.bladnummer, tekeningen.naam, max(datums.datum) as datum, datums.datumid
        into #ttDatSummary
        from tekeningen
            inner join datums
                on tekeningen.tekeningid=datums.tekeningid
        where tekeningen.bedrijvenid=@bedrijvenid
        group by tekeningen.bladnummer, tekeningen.naam, datums.datumid

    Select a.bladnummer, a.naam, a.datum, a.datumid
        from #ttDatSummary a
            inner join on (
                select max(datum) as datum, bladnummer, naam
                    from #ttDatSummary
                    GROUP BY bladnummer, naam
                ) b
                    on a.bladnummer = b.bladnummer AND a.naam = b.naam AND a.datum = b.maxdate
        order by a.bladnummer, a.naam
end
User is offlineProfile CardPM
+Quote Post

Hanzie
RE: Inner Join On Max(date)
12 Aug, 2008 - 06:15 AM
Post #4

D.I.C Head
**

Joined: 19 Aug, 2007
Posts: 92


My Contributions
Thnx for the quick reply!!

I indeed used it in a stored procedure.
It works perfectly.

I'm curious, what's the difference in speed between a temporary table and a view?
I believe i read somewhere they have somehow the same functionality.
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 12/2/08 08:35PM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month