Programming Help

Web Development



What's Here?

  • Members: 132,650
  • Replies: 471,760
  • Topics: 73,147
  • Snippets: 2,541
  • Tutorials: 667
  • Total Online: 1,150
  • Members: 59
  • Guests: 1,091

Who's Online?

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

Join 132,650 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,150 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
post 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

Go to the top of the page

Hanzie
post 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

Go to the top of the page

baavgai
post 12 Aug, 2008 - 05:48 AM
Post #3


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,967



Thanked 96 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 online!Profile CardPM

Go to the top of the page

Hanzie
post 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

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/23/08 05:13AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month