JOIN three tabels with only max date

Question

0.00/5 (No votes)

See more:

Hello
Please help
I can't do it by myself.
I have three tables:
Towary, Kontrahenci and TwrIlosci

The TWrIlosci table is linked to the first via id and contains entries with different dates.
I'd like to display only the entries for the last date

I have this query but it shows all entries

Sorry for that I forgot to paste data

<pre>
Table Towary
twr_kod			twr_nazwa	twr_numerkat	twr_koddostawcy		twr_iloscmin
MECCZ-TK7598/A	PAL												0.0000
MECCZ-TK7605	NAPY											0.0000
MECCZ-TK7722	KOLO 1											0.0000
MECCZ-TK7723	KOLO 2											0.0000
MECCZ-TK7741	ZEST											0.0000

Table Kontrahenci
knt_kod			knt_kntid
!NIEOKREŚLONY!	1
00000			1853
10-05-1039		2239
10-05-1052		2253
10-05-1090		2309

Table TwrIlosci 
twi_ilosc	twi_rezerwacje		twi_zamowienia		twi_data					TwI_TwrId
0.0000		0.0000				0.0000				2011-05-05 00:00:00.000		57
1.0000		0.0000				0.0000				2011-04-30 00:00:00.000		57
0.0000		0.0000				0.0000				2011-05-05 00:00:00.000		57
1.0000		0.0000				0.0000				2011-04-30 00:00:00.000		57
0.0000		0.0000				0.0000				2017-06-20 00:00:00.000		132

An example answer to my query

twr_kod	twr_nazwa	twr_numerkat	twr_koddostawcy	knt_kod	twr_iloscmin	twi_ilosc	twi_rezerwacje	twi_zamowienia	twi_data
MSP321528-0-00	ZTYLNIE			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSP321528-0-00	ZTYLNIE			6-10-11-MSP	0.0000	1.0000	0.0000	0.0000	2011-04-30 00:00:00.000
MSP321528-0-00	ZTYLNIE			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSP321528-0-00	ZTYLNIE			6-10-11-MSP	0.0000	1.0000	0.0000	0.0000	2011-04-30 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2017-06-20 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2012-05-22 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	10.0000	0.0000	0.0000	2012-05-17 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2017-06-20 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2012-05-22 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	10.0000	0.0000	0.0000	2012-05-17 00:00:00.000
MSPRM101572-012	CHWYTACZ C			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2015-05-07 00:00:00.000
MSPRM101572-012	CHWYTACZ C			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2015-05-07 00:00:00.000
MSPRM202092-0-1	CHWYTACZ G			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSPRM202092-0-1	CHWYTACZ G			6-10-11-MSP	0.0000	1.0000	0.0000	0.0000	2011-04-30 00:00:00.000
MSPRM202092-0-1	CHWYTACZ G			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSPRM202092-0-1	CHWYTACZ G			6-10-11-MSP	0.0000	1.0000	0.0000	0.0000	2011-04-30 00:00:00.000
MSPRM202095-013	N DOLNY			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2016-10-17 00:00:00.000
MSPRM202095-013	N DOLNY			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2015-10-19 00:00:00.000
MSPRM202095-013	N DOLNY			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2016-10-17 00:00:00.000
MSPRM202095-013	N DOLNY			6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2015-10-19 00:00:00.000

I would like to have only:

twr_kod	twr_nazwa	twr_numerkat	twr_koddostawcy	knt_kod	twr_iloscmin	twi_ilosc	twi_rezerwacje	twi_zamowienia	twi_data
MSP321528-0-00	ZTYLNIE	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSPRM100458-010	SRUBA	SCREW	RM100458-0-10	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2017-06-20 00:00:00.000
MSPRM101572-012	CHWYTACZ C	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2015-05-07 00:00:00.000
MSPRM202092-0-1	CHWYTACZ G	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2011-05-05 00:00:00.000
MSPRM202095-013	N DOLNY	6-10-11-MSP	0.0000	0.0000	0.0000	0.0000	2016-10-17 00:00:00.000

What I have tried:

SQL

SELECT
twr_kod, twr_nazwa, twr_numerkat, twr_koddostawcy, knt_kod, twr_iloscmin, twi_ilosc, twi_rezerwacje, twi_zamowienia, twi_data
FROM ((XL.Towary
INNER JOIN XL.Kontrahenci ON XL.Towary.twr_kntid = XL.Kontrahenci.knt_kntid)
INNER JOIN 
XL.TwrIlosci ON XL.TOWARY.Twr_TwrId = XL.TwrIlosci.TwI_TwrId)

Posted 8-May-18 21:21pm

Member 13818387

Updated 8-May-18 23:51pm

v4

Add a Solution

Comments

Maciej Los 9-May-18 3:29am

Sample data might help to resolve your issue.
At this momement a requirement to join tables only on max date is unclear, especially when there's no date field...

Jörgen Andersson 9-May-18 4:01am

Use "Improve question" instead of posting a new solution

Maciej Los 9-May-18 4:03am

Seems it's MS Access... Am i right?

Member 13818387 9-May-18 4:17am

Thanks for help
I changed the question. The database is MS SQL

Maciej Los 9-May-18 4:37am

You're very welcome. Please, next time use "Reply" widget if you want to inform me about your reply.
Now, your question is bit more understandable, but there's still few issues...
Does twi_data is a field on what you want to filter data? What you mean by saying: "I'd like to display only the entries for the last date"? Please, provide more details...

Member 13818387 9-May-18 4:50am

Yes twi_data is a field to filter data.
I'd like to get twr_kod with the last date from the twi_data field in response.
I will add in the question code what I receive and what I would like to receive.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-05-08T23:52:00

Solution 3

Seems that you need to use MAX() OVER()[^]:

SQL

SELECT DISTINCT twr_kod, twr_nazwa, twr_numerkat, twr_koddostawcy, knt_kod, twr_iloscmin, twi_ilosc, twi_rezerwacje, twi_zamowienia, twi_data
FROM (
    SELECT TwrI_TwrId, twi_ilosc, twi_rezerwacje, twi_zamowienia, MAX(twi_data) OVER(PARTITION BY TwI_TwrId ORDER BY twi_data DESC) AS twi_data
    FROM XL.TwrIlosci
) AS TWI
    INNER JOIN XL.TOWARY AS TWR ON TWR.Twr_TwrId = TWI.TwI_TwrId
    INNER JOIN XL.Kontrahenci AS K ON TWR.twr_kntid = K.knt_kntid

Posted 8-May-18 23:52pm

Maciej Los

Comments

Member 13818387 9-May-18 6:01am

Thank You
I will test it at home.
But please edit Your query.
There is no TwrI_TwrId column (second select)
I think it should be Twi_TwrId

Maciej Los 9-May-18 6:14am

Hmmm... There's a TwrI_TwrId field at the beginning of second SELECT statement.

Member 13818387 9-May-18 6:32am

Yes it's in the statement but there is no such column in table TwrIlosci

Maciej Los 9-May-18 6:56am

OK. Change above query as required... ;)

Member 13818387 10-May-18 9:20am

Is it possible to convert this query so that it works on MS SQL 2008 R2 I have an error and read that OVER CLAUSE does NOT Support ORDER BY for aggregate functions in SQL2008R2 (only for ranking)

Maciej Los 10-May-18 9:53am

Strange... As MSDN documentation states, OVER clause is supported starting from SQL Server 2008.

It's possible, but bit more complex. You need to replace second SELECT statement:

SELECT TwrI_TwrId, twi_ilosc, twi_rezerwacje, twi_zamowienia, MAX(twi_data) OVER(PARTITION BY TwI_TwrId ORDER BY twi_data DESC) AS twi_data
    FROM XL.TwrIlosci

with something like this:

SELECT TwrI_TwrId, MAX(twi_data) AS twi_data 
    FROM XL.TwrIlosci
    GROUP BY TwrI_TwrId) AS A
    RIGHT JOIN XL.TwrIlosci AS B ON A.TwrI_TwrId = B.TwrI_TwrId AND A.twi_data = B.twi_data

Member 13818387 10-May-18 10:32am

Should it work on both 2008 and 2017?
I checked only on 2017 but I've got an syntax error.

Maciej Los 10-May-18 10:36am

On Sql Server 2017 too? Oh no!
Give me some time and i'll improve my answer based on your sample data.

[EDIT]
Sorry, my bad!
My first statement was wrong, because it returns max date for each reord which is wrong. Use second statement.

Member 13818387 14-May-18 7:55am

When I replace this piece of code, I'll get a statement like this:
Hide Copy Code

SELECT DISTINCT twr_kod, twr_nazwa, twr_numerkat, twr_koddostawcy, knt_kod, twr_iloscmin, twi_ilosc, twi_rezerwacje, twi_zamowienia, twi_dataFROM (    SELECT TwrI_TwrId, MAX(twi_data) AS twi_data     FROM XL.TwrIlosci    GROUP BY TwrI_TwrId) AS A    RIGHT JOIN XL.TwrIlosci AS B ON A.TwrI_TwrId = B.TwrI_TwrId AND A.twi_data = B.twi_data) AS TWI    INNER JOIN XL.TOWARY AS TWR ON TWR.Twr_TwrId = TWI.TwI_TwrId    INNER JOIN XL.Kontrahenci AS K ON TWR.twr_kntid = K.knt_kntid

At the end there is one additional parenthesis

OriginalGriff · Answer 2 · 2018-05-08T21:39:00

Solution 1

Try:

SQL

SELECT TOP 1 ... ORDER BY MyDateColumn DESC

where your query fits in the ellipsis.

Posted 8-May-18 21:39pm

OriginalGriff